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

Feb 22nd, 2002, 02:34 AM

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 · Feb 22nd, 2002, 02:13 PM

Quote:

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.

Mar 1st, 2002, 01:45 AM

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.

Mar 1st, 2002, 02:44 AM

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/Replace with!

Thanks again.

Mar 1st, 2002, 05:31 AM

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

Quote:

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

Quote:

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 · Mar 1st, 2002, 06:04 AM

[quote]
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?
[quote]

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 22nd, 2002, 02:34 AM	#1
Guest Posts: n/a	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.

Mar 1st, 2002, 01:45 AM	#3
Guest Posts: n/a	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.

Mar 1st, 2002, 02:44 AM	#4
Guest Posts: n/a	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/Replace with! Thanks again.

Mar 1st, 2002, 06:04 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,314	[quote] 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? [quote] 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.