Indirect To Another Workbook

[MikeG]

I would like to use the INDIRECT function to refer to other open workbooks.

In cell A1 I would like to be able to type in the target workbook name - e.g. Book2
In cell A2 I would enter the worksheet name within that woorkbook - e.g. Sheet3

I want to pull data from cells M1 to M10 of the target workbook, so in my current workbook I plan to enter the references in C1 to C10 as follows:

M1
M2
M3
etc.

So what I need help on is the INDIRECT formula that would go in, say, D1 to D10 that would refer to the current values in A1, A2, and the adjacent C column.

Then whenever I enter new values in A1 or A2, it will automatically pull in the values from M1 to M10 in the selected workbook.

Hope this makes sense.

Thanks,

MikeG

 

[Andrew Poulsom]

Try:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&C1)

 

[MikeG]

Try:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&C1)

Perfect!

Thanks very much Andrew.

 

[MikeG]

One other question. Sometimes I need to refer to a regular xlxs workbook, and sometimes I refer to a xlsm workbook.

If I enter xlxs or xlsm in cell A3, could you tell me how the INDIRECT formula would change?

Thanks,

Mike

 

[Andrew Poulsom]

Try:

=INDIRECT("'["&A1&"."&A3&"]"&A2&"'!"&C1)

 

[MikeG]

Try:

=INDIRECT("'["&A1&"."&A3&"]"&A2&"'!"&C1)

Thanks again!