# Thread: Vlookups on multiple worksheets

1. Help!!

In the attached workbook i have created dependant drop down lists ('Graphs' tab) using named ranges etc from the 'lookup' tab. On the last criteria selected i then need to lookup the data for that criteria and make some graphs from it. What i cant seem to work out is how to lookup that cell across the 10 worksheets and bring back that data.

I did think about INDIRECT formula but i can get it to work properly as i am looking for a range of data rather than just one cell's data.

Any ideas....?

2. In cell C21 on the Graphs sheet:

=OFFSET(INDIRECT("'"&\$C\$5&" "&\$C\$11&"'!\$BH\$1"),MATCH(\$C\$16,INDIRECT("'"&\$C \$5&" "&\$C\$11&"'!A:A"),0)+ROW()-22,0)

and fill down to C43.

There's a lot of repetition in these formulas; you could store the sheet name formula =C5&" "&C11 in a cell named SheetName and the offset of the doctor's name =MATCH(\$C\$16,INDIRECT("'"&SheetName&"'!A:A"),0) in a cell named NameOffset. The formula in C21 then becomes a lot shorter:

=OFFSET(INDIRECT("'"&SheetName&"'!\$BH\$1"),NameOffs et+ROW()-22,0)

3. Thank you Hans for your speedy response...however I am now slightly lost and do not understand the part in the formula that references cell BH1. Plus it didnt return anything at all which has made me even more confused!

=OFFSET(INDIRECT("'"&\$C\$5&" "&\$C\$11&"'!\$BH\$1"),MATCH(\$C\$16,INDIRECT("'"&\$C \$5&" "&\$C\$11&"'!A:A"),0)+ROW()-22,0)

Also which i failed to mention - i need to go across to column BB on the doctors/wards/specialty splits as well - so i just need to lift that entire section from the particular criteria chosen....

Kirsty

4. I had assumed that you just wanted the end total from column BH (hence the \$BH\$1 in the formula). If you want the entire range from column H to column BH (not BB, I think), you can use

=OFFSET(INDIRECT("'"&SheetName&"'!\$H\$1"),NameOffse t+ROW()-22,COLUMN()-3)

See the attached version (I removed most of the sheets to reduce the file size, but the idea remains the same).

5. Brilliant Hans - you are a genius! exactly what i was after - thanks ever so much!! I totally understand it now!

K

6. Hans,

This still isnt working....i have done exactly what you said and have created Sheetname and Nameoffset, however when you do the match, the figures do not reconcile with the actual base data...is it something to do with the counting of the rows and columns?

Please see attached...im working as a test on the surgical wards f5 hardy ward.....

7. The table on the Graphs sheet has been moved up 3 rows from the version you originally attached. This means that the row offset has to be changed from -22 to -19: the formula for C18 (originally C21) should now be

=OFFSET(INDIRECT("'"&SheetName&"'!\$C\$1"),NameOffse t+ROW()-19,COLUMN()-3)

8. wow....i didnt get that bit....22 rows from the top and three columns across....of course because i had 'tidied' it up and deleted some rows that changed the count from the top....excellent - its been driving me bonkers...

you've been brilliant - thanks ever so much.

Kirsty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•