How can I use the drop down cell selection in a formula?

[Red Dianthus]

I have created a dropdown box which allows me to choose from the list
of tabs -- each tab represents a month. I want to use that drop down
selection in a vlookup....=MAX(IF('Aug 2009'!G45:G143=G9,'Aug 2009'!
B45:B143,FALSE)) How can I replace the 'Aug 2009' so that the formula
uses the month/tab selected from the dropdown menu?

Thanks!

 

[Shane Devenshire]

Hi,

Try something like this

=MAX(IF(INDIRECT("'"&F7&" 2009'!G45:G143")=G9,INDIRECT("'"&F7&"
2009'!B45:B143"),""))

where F7 contains your drop down list of sheet names as 3 digit text. This
is an array formula which means you must enter it by pressing
Shift+Ctrl+Enter, not Enter.

If the sheet names include the year then use

=MAX(IF(INDIRECT("'"&F7&" 2009'!G45:G143")=G9,INDIRECT("'"&F7&"
2009'!B45:B143"),""))