Excel 2003 - Reference a sheet described in a cell

[PaultheS]

I'm wondering if there's any way to decide which sheet to reference based on a formula.

Specifically, I have data for sales in sheet 'All Sales'. Then, I have a sheet outlining cost assumptions for each month. They all have identical format, and the sheet names are 'Costs - January', 'Costs - February', etc.

What I would like to do is a vlookup based on the type of sale and the month the sale was made in. Something like "=VLOOKUP(A2,'Costs -' & TEXT(D2,"mmmm")!A2:C20,3,FALSE)" (but this formula doesn't work). Is there a way I can pick which sheet in looks at in a formula? Or any other suggestions to get around this problem?

 

[SkipVought]

Hi,

Check out the INDIRECT function.

Skip,
[sub]
[red][/red]
[/sub]

 

[PaultheS]

Hi Skip,

Thanks for the response. I had tried playing with the INDIRECT function but I couldn't figure out the logic. Just now I had an idea to make a cell with the range as the text (e.g. cell W2 is ="'Costs - " & TEXT(D2,"mmmm") &"'!A2:C20") and then I have the formula as

=VLOOKUP(A2,INDIRECT(W2),3,FALSE)

and it works! Thanks again!