Excel Excel 2010-Vlookup, Indirect, Workbook Reference

[atrever]

Hi MVPs,

This is a tuff one. Here's what I'm trying to accomplish.

I have two workbooks. In local workbook I have a dropdown selection for Month, in Cell G1. I am wanting to use this field for my indirect reference, as when a different month is selected from the list I want that selection to replace the worksheet name (which is in my local workbook. Sales Schedules.xlsx is the other workbook.

It works when I hard code the month (the month's are abbreviated, Jan, Feb, Mar, etc)

=VLOOKUP(D8,'[Sales Schedules.xlsx]Mar'!$W$13:$AC$23,7,0)

trying many variations like...

=VLOOKUP(D8,'[Sales Schedules.xlsx]&indirect(G1)&'!$W$13:$AC$23,7,0)

I'm hoping it's just a formatting issue, but, I suspect it has to be done a different way. I think the question can be framed: Can you use a local indirect cell reference to replace a sheet name on a workbook connection? Any suggestions?

I'm using G1 as an indirect reference for pulling in other data from the local workbook which has sheets for the months, such as =INDIRECT(G1&"!E28"), and this is working fine. thanks in advance for any suggestions you may have (I've also been unsucessful to get the code to work by using VB and macros, which update the hardcoded sheet name in the first function above (i.e. a macro that is triggered when the G1 box changes month to say Feb, kicking off a Feb macro that updates the above function to replace Mar with Feb).

Hope this makes sense!

 

[alow]

Have you tried putting the sheet name inside the indirect also?
=VLOOKUP(D8,INDIRECT("'[Sales Schedules.xlsx]"&G1&"'!$W$13:$AC$23"),7,0)
Part of the problem with CONCATENATE (&) is that it combines values or text, and since the cell ranges weren't in quotes, it would try to combine all the values with the sheet name reference. I believe trying the formula like this should solve your problem.