Blogger
Share this content
0
7
58745

Cell reference to another file in Excel?

Is it possible to include within a formula a link to another file without specifying the file in question within the formula? ie: Can I have a formula in, say, cell B1 that will look at cell A1 to establish which file it needs to look at to find the required information.

EG: Cell A1 = February.xls

I need cell B1 to look at information contained in February.xls but next month A1 will change to March.xls and therefore update the information automatically (currently done manually, yawn)

The file was created by a colleague who doesn't want the file to change format so this is the easiest solution I can think of to update the formulae within the file without having to go into each cell and change each formula every month which is how it has been done so far. (PS: I know this colleague could simply use find and replace to change information but I'm not sure if he's thought of that yet!!!!)
danny moynes

Replies

Please login or register to join the discussion.

avatar
23rd Feb 2004 17:05

Neil Boyd, are you there?
Neil:

Thank you for your response. However, is there a way to include a sum within this function or does it only return text?

Thanks (0)
avatar
By ACDWebb
23rd Feb 2004 11:08

Isn't your change only one action anyway
Rather than manually edit each formula all you need to do is select
Edit
Links
and then the Change Source... button and select your March file.

That will then update your Links from February.xls to March.xls

Thanks (0)
avatar
23rd Feb 2004 14:41

External file cell reference - INDIRECT function
Danny:

The INDIRECT function is what you are looking for.
If the following demo does not make sense (due to formatting), let me know and I'll e-mail you a screenshot.

There might be a neater way of achieving the same result, but this has worked well for me.

Regards,
Neil Boyd

Background:
Two separate files have been set up (January.xls & February.xls)
The cell to which I wish to refer is A1 on SHEET1 of each file.


The method:
COLUMN A (for the lookup) COLUMN B
January =INDIRECT(CONCATENATE("[",A4,".xls]Sheet1!$A$1"))
February =INDIRECT(CONCATENATE("[",A5,".xls]Sheet1!$A$1"))


The result:
COLUMN A (unchanged) COLUMN B
January This cell exists in the January file.
February This cell exists in the February workbook.

Thanks (0)
avatar
24th Feb 2004 11:28

More on INDIRECT - and a word of caution....
Danny,

The INDIRECT function can be used to build (virtually?) any formula - including SUM.... as long as you know what the formula would look like (square brackets and exclamation marks get me every time!).
It doesn't have to return a text value.

INDIRECT is a function that simply "tells" Excel to treat whatever text you have entered in a cell as if it was entered as a formula. Concatenation is one way to build up the text string, so that you can reconstruct the syntax for your desired function.

I really ought to have caveated my original posting:

INDIRECT is an interesting exercise - even if just to show that IF ALL ELSE FAILS you can make Excel bend to your will. But, as likely as not, there is a simpler way of doing whatever it is that has stumped you. Other respondents have suggested updating links or using Search & Replace - both of which I would recommend in most cases over INDIRECT.

I hope this helps.

Neil

Thanks (0)
avatar
By ACDWebb
23rd Feb 2004 18:00

I still think you will have a much easier & quicker task by chan
or have I completely misunderstood what you want to do.

I created 3 files called Test.xls February.xls and March.xls

The Feb file has the numbers 1 - 5 in cells B1 - B5 on Sheet1

The March file has the numbers 6 - 10 in cells B1 - B5 on Sheet1

The test file is initially set up with the formula =SUM([February.xls]Sheet1!$B$1:$B$5) in Cell B2 and gives the result 15

All the files are saved, then I select Edit Links with the Test file open, click on the Change Source... button, select March.xls and click OK

The formula in cellB2 is automatically changed to =SUM([March.xls]Sheet1!$B$1:$B$5) with an amended result of 40

Is that not what you are after?

I realised this after having tried to write a macro to automatically change all the links in a file only to realise that it was completely unnecessary because of the above.

I should have thought that you run a real risk of hitting problems by trying to build in a reference to a file in a call as part of the formula as you suggest, and also possibly slowing the file down depending on its size

Thanks (0)
avatar
By neileg
24th Feb 2004 10:13

Indirect
Please note that INDIRECT will return an error if the referenced file is not open.

Thanks (0)
avatar
By neileg
24th Feb 2004 10:17

Back to front
An alternative approach is to leave the source workbook with the same name, and change the contents.

So you could have Currentmonth.xls as the source file. When you want Feb's data, copy the Feb workbook to Currentmonth.xls, when you want March, copy the March workbook, etc. Include the month as a field in the data so you don't loose track of which dataset you are using.

I did this for years using SuperCalc, so that'll give you a clue as to how long ago that was.

Thanks (0)