How do I reference a filename inside a cell from a formula

[Guest]

Hi,

I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

Example

A1: filename.xls

B2: =+'filename.xls'!C1
C2: =+'filename.xls'!C2

I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

I don't know macros so I haven't touched on it.
Thanks.

 

[Chris Davison]

On 2002-02-22 01:34, Anonymous wrote:
Hi,

I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

Example

A1: filename.xls

B2: =+'filename.xls'!C1
C2: =+'filename.xls'!C2

I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

I don't know macros so I haven't touched on it.
Thanks.

see if this helps out :

in B2 enter : =INDIRECT(""&A1&"!C1")
in C2 enter : =INDIRECT(""&A1&"!C2")

I used INDIRECT earlier today with the help of one of Aladin's postings on the old board.

 

[Guest]

Hello Chris,

Sorry for the delayed post as I only got to read your reply only today. I've tried your suggestion and after figuring out the order of the single quote and double quotes, it initially gave a #REF error. Then checking the help file regarding INDRECT, I understood that you have to open the external reference and so when the file was opened, it worked!

The problem now is that the files I am working with are daisy chained. Ie. balances from JAN02.XLS are carried over to FEB02.XLS, and so on. If I don't open the previous files, then I'll get reference errors. Any suggestions?

Thanks for your time and help.

 

[Guest]

Hi Chris,

Its ok with my problem now. After I've read Brian from Maui's post Re: Funny story, it just solved my problem. I just have to Edit/Replace/Find<data>/Replace with<new data>!

Thanks again.

 

[Guest]

I have been trying to get this to work but with filename and sheetname. Is it possible?

On 2002-02-22 13:13, Chris Davison wrote:

On 2002-02-22 01:34, Anonymous wrote:
Hi,

I'm trying to make a template and is looking for a simple way to reference a cell containing the filename and use it in a formula.

Example

A1: filename.xls

B2: =+'filename.xls'!C1
C2: =+'filename.xls'!C2

I would like to be able to just change the filename in cell A1 so that all the formula in the worksheet will be automatically updated with data from the new file.

I don't know macros so I haven't touched on it.
Thanks.

see if this helps out :

in B2 enter : =INDIRECT(""&A1&"!C1")
in C2 enter : =INDIRECT(""&A1&"!C2")

I used INDIRECT earlier today with the help of one of Aladin's postings on the old board.

 

[Aladin Akyurek]

On 2002-03-01 04:31, Anonymous wrote:
I have been trying to get this to work but with filename and sheetname. Is it possible?

Lets say that you have in

A1: [file1.xls]Budget
A2: [file1.xls]
A3: file1.xls
A4: Budget

=INDIRECT("'"&A1&"'!H10")

will retrieve the value in H10 from sheet named Budget in file1.xls.

=INDIRECT("'"&A2&A4&"'!H10") will do the same.

=INDIRECT("'["&A3&"]"&A4&"'!H10") will do the same.