Summary Page

[proctk]

HI,

On a sheet I have a data list that I'm trying to use formulas to create a summary page based on validation values selected. First thought is I could use a pivot table but I can seam to format it to look right.
In Cell C5 I validation value and in Cell E5 I have another validation value

I want a formula that will keep looking down the list and return matches. .

I hope this makes sense.

 

[proctk]

Alright, I'll try and explain this better. I want to lookup all records one sheet that matches criteria and return the values to another sheet. I could use a Pivote table but formating is an issue and there is no data anylsis required. My goal is to create a summary page.

Any direction is great. I thought about using lookup or sumproduct but I don't know how to get the formula to move down the record set.

 

[PCRIDE]

You can use VLOOKUP if you know how to use that.

 

[proctk]

Yeh, I gave that a try and the problem is there will be more then one record in the data set that matches the critera. I need to bring all values over to the summary sheet.

Example Data

name 1
lookup value Result

name 2 Result 1
name 1 Result 2
name 2 Result 3
etc.. Etc...

So when the user looks up Name 2 row 1 should have the value Result 1 and row 2 should have the value result 3.


Thank you for your reply

 

[proctk]

I found this

=INDEX('Event List'!$A$3:$I$1000,SMALL(IF('Event List'!$B$3:$B$1000=$C$5,ROW('Event List'!$B$3:$B$1000)),ROW(1:1)),4)


http://office.microsoft.com/en-us/e...ultiple-corresponding-values-HA001226038.aspx

The problem is I don't think it will return a text value. Any thoughts

 

[proctk]

Okay,

I'm working with this know

={IF(ISERROR(INDEX('Event List'!$A$3:$I$1000,SMALL(IF('Event List'!$B$3:$B$1000=$C$5,ROW('Event List'!$B$3:$B$1000)),ROW('Event List'!1:1)),4)),"eRROR",INDEX('Event List'!$A$3:$I$1000,SMALL(IF('Event List'!$B$3:$B$1000=$C$5,ROW('Event List'!$B$3:$B$1000)),ROW('Event List'!1:1)),4))}

For some reason I'm only getting 0 as a results and it should be returning something, I think this could be creating the issue ROW('Event List'!1:1)),


Help please

 

[PCRIDE]

Try using a Sumifs, you can add up different values based on multiple critera.

Sumifs(Sum range, Critera Range1, Criteria 1, Criteria Range2, Criteria 2 etc...

Criteria Range will be the category i.e Apples, ORanges etc..
Criteria would be the dashboard or summary page

Apples 30
Oranges 25