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

G

Guest

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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top