Sheet reference in formula using indirect cell with a date

[theAphex]

I have a primary worksheet with multiple dates going across row 2, and 30 additional sheets, one for each day (in the format 1-Sep, and so on). I need a formula below each date that uses the date in that column to look at the appropriate sheet for the calculation.

For Example, the date (1-Sep) is contained in Cell C2, when I use the formula as follows, i get a #REF

=VLOOKUP($B4,(INDIRECT("'" & C2 & "'!$B$5:$0$150")),13,FALSE)

i would like the formula to interpret that as;

=VLOOKUP($B4,'1-Sep'!$B$5:$0$150,13,FALSE)

Any ideas? It looks like it might be taking the date and changing 1-Sep to 40787

 

[pclplante]

You have a zero as the letter reference you have $B$5:$zero$150 I think it should be an o not a 0 see the difference O 0 even if it is capped.

 

[T. Valko]

I have a primary worksheet with multiple dates going across row 2, and 30 additional sheets, one for each day (in the format 1-Sep, and so on). I need a formula below each date that uses the date in that column to look at the appropriate sheet for the calculation.

For Example, the date (1-Sep) is contained in Cell C2, when I use the formula as follows, i get a #REF

=VLOOKUP($B4,(INDIRECT("'" & C2 & "'!$B$5:$0$150")),13,FALSE)

i would like the formula to interpret that as;

=VLOOKUP($B4,'1-Sep'!$B$5:$0$150,13,FALSE)

Any ideas? It looks like it might be taking the date and changing 1-Sep to 40787

Try it like this...

=VLOOKUP($B4,INDIRECT("'"&TEXT(C2,"d-mmm")&"'!B5:O150"),13,0)

BTW...

In your posted formula the reference to O150 is using zero instead of the letter O, zero150.