HLookup indirect multiple worksheets

K

Kelly

I need to accumulate the data from 2,000lease + worksheets onto one summary
worksheet. I would like to insert the lease number and use HLOOKUP to
populate the summary sheet. I'm not familiar enough with INDIRECT to put it
in the formula. I copied the following from another community posting and
tried to adjust it for my summary sheet:
=HLOOKUP(A5,INDIRECT($A$5&"!$d$4:$h$104"),2,FALSE)
The worksheets contain 6 number (e.g. 001500).
Can you please help me to write the correct formula to pull each piece of
data from the worksheets' columns D through H to the summary sheet?
Thanks,
Kelly
 
M

Max

If in A5 down are text numbers eg: 001500, try:
=HLOOKUP(A5,INDIRECT("'"&A5&"'!D4:H104"),2,FALSE)
Copy down

If in A5 down are real numbers eg: 1500, then try:
=HLOOKUP(TEXT(A5,"000000"),INDIRECT("'"&TEXT(A5,"000000")&"'!D4:H104"),2,FALSE)
 
K

Kelly

The 001500 is the first lease number that will have its own worksheet (and
continues through lease 051106 ... 2024 leases!)
When I copied your first and second formulas into the spreadsheet, I got
#N/A in the cell (B5).
What am I doing wrong?
Thanks,
Kelly
 
K

Kelly

Thank you very, very, very much! I had to add the lease number at the top of
the column and everything worked! I can't tell you how much easier you just
made my job!
Kelly
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top