How to replace workbook tab name in a formula with a cell reference

[JJ145]

Hi,

I bet there are some answers lingering out there but I honestly havn't been able to locate the answer. So my problem is I have two workbooks. Workbook 1 has over 50 different tabs that are named as US states. Each tab within that workbook are structrued the same. Workbook 2 is an raw output from some software I have, but lists the state name in one of the columns. What I essentially want to do is have a way that automatically updates the tab name in a several different formulas, so I can drag and drop the formula instead of having to change the formulaeach time a new state starts. So basically I want to edit the state part automatically based on values in an adjacent column: =INDEX[Workbook 1.xlsx]State'!$C:$C,MATCH(blah blah)). I've tried inserting a direct reference in place of "State" such as D31, but it didn't work. I don't know if this is at all possible or requires VBA skills. Let me know your thoughts

[menem]

If you can merged them together, you can use INDIRECT to reference cross sheet (not cross workbook).
Because INDIRECT only work when in the same workbook or both workbook are opened.

The formula will be like...

=INDEX( INDIRECT("'"&D3&"'!C:C") , Match( .... ) )

Regards.