PDA

View Full Version : Solved: Returning data based on multiple criteria



Lawrence
07-17-2008, 03:49 PM
“Data Sheet” is compiled by looking at multiple sheets and extracting the necessary data.

In “Graph1”, the same data as in “Data Sheet” is rearranged in columns Q through AA, and then a VLookup is performed to get the output in the black box. Although it works, it seems very inefficient. What I am looking for is to bypass columns Q through AA and go straight to the output with something like this:

Cell F40: Look for May-08 (Cell F39) in “Data Sheet”, once you find it, look for Bolts (Cell C40) in “Data Sheet”, once you find it, look for Total (Cell 39) in “Data Sheet”, and return the appropriate value. Same formula for cells F41-F43.

For the prior months (Columns G-M), the same formula would be used or once it found the appropriate cell in “Data Sheet” to populate cell F40, the formula could “go up” 9 rows and return that value in Cell G40, and another 9 rows and return that value in cell H40, and so on.

In “Graph2” I’d like to do something similar to populate the table in B53:I76.

Is this possible or do I have to still manipulate the data?

Dr.K
07-17-2008, 04:53 PM
Hmmm, whats your source data?

I'm not sure exactly what you want, but if you really need "multiple criteria", it might be better to use SQL to get the data you need. You can use ADO to pull data from text files and XLS files, not just DBs.

Thanks for the sample workbook, its excellent spreadsheet work! It should be possible to automate this. I'm going to take a crack at your workbook, if only because I want to steal one of your graphs. :devil2:

Lawrence
07-18-2008, 11:28 AM
Well, I don't know if this is the most efficient, but I have found a solution. In "Graph1", I created a helper table (cells S7-Y8) and inserted the following in cell E40 and copied down (changing the +1 to +2, +3, and +4) and across:

=INDEX('Data Sheet'!$A$1:$J$500,MATCH(E$39,'Data Sheet'!$A:$A)+1,HLOOKUP($C$39,$S$7:$Y$8,2,0))

Now, I just have to do something similar for "Graph2".

Bob Phillips
07-18-2008, 11:54 AM
You could use this I guess (in cell E40)

=INDEX('Data Sheet'!$A:$J,MATCH(Graph1!$E39,'Data Sheet'!$A$1:$A$218,0)+ROW(A1),HLOOKUP($C$39,$S$7:$Y$8,2,0))

but I would also do two things. I would have a cell somewhere that had this formula

=HLOOKUP($C$39,$S$7:$Y$8,2,0)

and refer to that cell, and in E38:L38 I would add the formula

=MATCH(Graph1!$E$39,'Data Sheet'!$A$1:$A$218,0), maybe even colouring the font white to hide it, and use that in the formula. Net effect

=INDEX('Data Sheet'!$A:$J,e$38+ROW(A1),$Z$1)

This will considearbly improve recalculation not having to calculate all those cells each time.

Lawrence
07-18-2008, 12:41 PM
Bob - Thanks for the more efficient way.

Quick question: I have added a DV to select the months to be displayed. I was trying to add a name range called AllDates where the formula would be ='Data Sheet'!$A:$A and then call AllDates in the DV. The problem is that all the blank rows show up. Can I get rid of those?

In the meantime, I resorted to a 13-month list with the following in each cell:

P14: =LARGE('Data Sheet'!A:A,1)
P15: =DATE(YEAR(P14),MONTH(P14)-1,DAY(P14))
P16: =DATE(YEAR(P15),MONTH(P15)-1,DAY(P15))

and so on...

Bob Phillips
07-18-2008, 01:03 PM
Define AllDates as

=OFFSET('Data Sheet'!$A$1,,,COUNTA('Data Sheet'!$A:$A),1)

Lawrence
07-18-2008, 01:15 PM
No worky, it gives me blank values and doesn't include evrything :aw

Bob Phillips
07-18-2008, 02:01 PM
Just looked at the spreadsheet, didn't appreciate the layout before. You would need to extract the data to a separate range, and lik the DV to that.

Lawrence
07-18-2008, 03:13 PM
That's what I did (see formulas in post #5) since I couldn't make it work any other way. Thanks for all the help.