Automated file name retrieval and updates
 

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!

I have just figured out how to make the cell reference work through the
additional row and column. The concatenate was not required at all. Obvious
looking at it now! Anyway, unless anyone knows how to do a similar thing but
without the additional row and column that would be great, otherwise I think
I'm sorted now.

Thanks for reading.

"Ginger" wrote:

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!

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!

depending on what your books and sheets are called and what format you want
the results to be in, there are several ways to do it.
This was set up to be a table. if you wnat just a column or row of final
results you can simplify your eqautions


"Ginger" wrote:

I have just figured out how to make the cell reference work through the
additional row and column. The concatenate was not required at all. Obvious
looking at it now! Anyway, unless anyone knows how to do a similar thing but
without the additional row and column that would be great, otherwise I think
I'm sorted now.

Thanks for reading.

"Ginger" wrote:

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!