Hi,

I was wondering whether it was possible to use a file name generated by a
formula or, collection of formulae, as reference to an external spreadsheet.

For example, if I have a different sheet for each month of data within a
different spreadsheet file for each year is it possible to fill in the
constituent parts of the file name and sheet name to get the correct data? I
am hoping to be able to to this from column and row headings ideally.

Many thanks in advance!

check out the indirect() function
if the worksheets are listed in row 1
and the workbooks are listed in columnA
=indirect(A2&B1&"B2)
would get you the data from workbook A2 and Worksheet B1 cell B2
Note that you will have to set the format for the cells properly.

"Ginger" wrote:

Hi,

I was wondering whether it was possible to use a file name generated by a
formula or, collection of formulae, as reference to an external spreadsheet.

For example, if I have a different sheet for each month of data within a
different spreadsheet file for each year is it possible to fill in the
constituent parts of the file name and sheet name to get the correct data? I
am hoping to be able to to this from column and row headings ideally.

Many thanks in advance!

Hi bj, thanks for the reply.

I have tried using this function and have had some success. Two things seem
to be problems as far as I can tell.

Firstly it appears that the related spreadsheets all need to be open for
this to work. That isn't a big problem but it would be nice to avoid if at
all possible!

Secondly I am having trouble getting the AutoFill to adjust the cell
references automatically. I think that because the cell reference is text and
I am trying to adjust it by dragging it is assuming that I want the text to
remain the same. I therefore thought I would try to have a formula to get the
correct reference from an addition column and row and create the cell
reference using Concatenate() but I can't make this work either!

Any suggestions greatfully received!!

"bj" wrote:

check out the indirect() function
if the worksheets are listed in row 1
and the workbooks are listed in columnA
=indirect(A2&B1&"B2)
would get you the data from workbook A2 and Worksheet B1 cell B2
Note that you will have to set the format for the cells properly.

"Ginger" wrote:

Hi,

I was wondering whether it was possible to use a file name generated by a
formula or, collection of formulae, as reference to an external spreadsheet.

For example, if I have a different sheet for each month of data within a
different spreadsheet file for each year is it possible to fill in the
constituent parts of the file name and sheet name to get the correct data? I
am hoping to be able to to this from column and row headings ideally.

Many thanks in advance!