Indirect reference to workbook and worksheet

**Peanuts890** · 04-16-2010, 08:14 AM

I'm a newbie so bear with me while I try to use the forum correctly - thanks

I am trying to reference a cell (or compare a number of cells) in two different workbooks called "Actuals" and "Budget" each of which has 12 tabs named "Jan" to "Dec". In my new workbook I would like to hold the name of the workbook to be referenced in cell "A1" and the name of the tab in cell "A2".
ie. A1="Actuals"; A2="Jan".
For example I would like to reference cell G21 in the tab "Jan" of workbook "Actuals" and compare it with G21 in the tab "Jan" of workbook "Budgets".
Next month this would be repeated changing the tab to "Feb".

Hope this all makes sense.
Thanks

**JBeaucaire** · 04-16-2010, 01:51 PM

The syntax would be:

=INDIRECT("'[" & $A$1 & ".xlsx]" & $A$2 & "'!G21")

This creates a text string of the reference pieces, inside the INDIRECT() it is assembled and then the INDIRECT() converts it back to an actual usable reference.

PROBLEM:
These formulas will only work if the Actuals.xlsx file is open.

**Peanuts890** · 05-04-2010, 05:23 PM

Thanks for excellent advice - has generated another query:
Can this form of indirect addressing be carried out on a workbook or file that is not open.