HLOOKUP across multiple sheets

[herkiefan]

I have daily data in column format that crossess over into two separate worksheets (since Excel does not allow for more than 250-ish columns in one sheet). I need to create an HLookup formula that can use multiple sheets as the range. However, I am having trouble making this work. Is it even possible?

Assuming that the first 6 months are on Jan-Jun05 and the second 6 months are in Jul-Dec05; the dates are in row one and the data starts directly below the date row. Each 6-month segment is the same size. The example below is trying to find the data in row 9 based off of the date in cell B1.

=HLOOKUP(B1,'Jan-Jun05:Jul-Dec05'!C1:GA52,9,FALSE)

but it returns #VALUE!

thanks for any and all help I may receive,

Mike

Also, there are more units of rows of data than there are columns in an Excel sheet, so transposing the data to a row format would not work.

 

[SkipVought]

Hi,

"I have daily data in column format that crossess over into two separate worksheets..."

OUCH!

Similar data in more that one place -- OUCH!

You are experiencing the pain, which is why you should NEVER segment your data like this. NEVER!

If you want to REPORT your data in different sheets, OK.

But the source data ought to be in a single, well-designed table.

Best you can do now, short of a redesign, is do separate lookup on eash sheet.

Skip,

_[red][/red]

 

[herkiefan]

Would I use an IF statement with an HLookup imbedded then?

 

[herkiefan]

skip,
thanks for the input, the IF statement was able to pull the correct information. I can't wait till the upper brass ok's Excel 2007's purchase.