HLookup indirect multiple worksheets

[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

 

[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)

 

[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

 

[Max]

It should have worked.
See this quick sample:
http://cjoint.com/?fzgGs7yxDe

 

[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

 

[Max]

Great to hear that. You're welcome.