Excel - Links to other Formulas

[suzey]

Dear All,

I currently have various excel files, in a set file structure. All of these files currently have links to each other and also a link to a summary document.

We need to copy the whole file structure, and rename the files. Currently I would then need to go into each workbook and change the links.

I would like to be able to put a bit more intelligence into each workbook, and type in the filepath and name of the file into a single cell in the workbook, and then change all the formulas with links in them, so they reference this one cell and then have the rest of the formula, sheet and cell ie:

='c:\Temp\Theworkbook!Sheet1$a$2

I would like to be able to do something like :

=A2 & "Sheet1$a$2"

As we intend to copy the whole structure quite regularly, this would save a lot of time, if only one cell in a workbook needed to be changed.

I have tried entering the formula as above, but it just displays it as text, is there a way to force it to recognise it as a formula?

Has anyone tried anything like this before? Or is there another way to do this?

Thanks in advance



Sue

 

[xlbo]

Try looking at the INDIRECT function - not sure whether it'll work on external workbooks but if you have a path set in A2 then
=INDIRECT(A2 & "Sheet2!$A$2&quot
should work

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[suzey]

Thanks Geoff,

Unfortunately it does not want to work. Any other ideas?

The only other thing I can think of is writing a macro to do everything for me.


Sue

 

[xlbo]

Bit of syntax checking - this works:
=INDIRECT("'[" & A2 &"]Sheet2'!$A$2&quot

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[suzey]

Thanks again Geoff,

Unfortunately I still cannot get this to work, have I missed something.

In cell C1, I have the following typed in :

d:\temp\thefilename.xls

then in the formula I have :

=INDIRECT("'[" & C1 &"]Sheet1'!$H$6&quot

I've also tried just putting the filename in c1 and changing the formula to :

=INDIRECT("'d:\temp\[" & C1 &"]Sheet1'!$H$6&quot

I'm sure it'll be something simple I've missed.




Sue

 

[xlbo]

Is the file open ???
It needs to be open to update

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[suzey]

Geoff,

Yep, I've tried with the file open and closed, no difference.

I think I'll be putting this one down as a 'not possible' for now, but thanks for all your help.



Sue

 

[GlennUK]

Sue,
Geoff is right, this is possible when the file is open, but any spelling mistake will prevent the formula from working.

Check the exact spelling of the folder name, the file name, and the sheet name.


Glenn.