HLookup indirect multiple worksheets
 

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

HLookup indirect multiple worksheets
 

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,"0 00000")&"'!D4:H104"),2,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelly" wrote:
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

HLookup indirect multiple worksheets
 

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" wrote:

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,"0 00000")&"'!D4:H104"),2,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelly" wrote:
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

HLookup indirect multiple worksheets
 

It should have worked.
See this quick sample:
http://cjoint.com/?fzgGs7yxDe
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelly" wrote:
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

HLookup indirect multiple worksheets
 

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" wrote:

It should have worked.
See this quick sample:
http://cjoint.com/?fzgGs7yxDe
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelly" wrote:
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

HLookup indirect multiple worksheets
 

Great to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kelly" wrote:
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