Excel Linked Cells

jrgutt · Apr 22nd, 2002, 02:27 PM

We have a number of excel spreadsheets which contain links to other spreadsheets. In the future, we are planning on changing our directory structure which will cause problems for the cells that contain links to other spreadsheets. Is there a simple way to identify the cells which have links in them, the link itself, and a someway to change the links without having to change every linked cell in every spreadsheet?

Chris Davison · Apr 22nd, 2002, 03:09 PM

if it were me, with 10 files, I'd open them all up then save them to the new directory.... all the links would change automatically.

My guess is you have a lot more than 10 though, right ?

RET79 · Apr 22nd, 2002, 03:26 PM

Why dont you put the file path being linked to in A2 say, like

C:MyFile2001myfile.xls

then, use indirect function to link.

For instance

=INDIRECT(A2,T65)

I think would pick out T65 from C:MyFile2001myfile.xls
which is in A2.

So next year, just put the new path in A2, and your whole spreadsheet will be updated.

RET79

RET79 · Apr 22nd, 2002, 03:38 PM

I think what I am describing is parameterising file names.

It's very effective, and rigid. Changing one cell with the file path updates the whole sheet or book.

wicked

RET79

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

Apr 22nd, 2002, 02:27 PM	#1
jrgutt New Member Join Date: Apr 2002 Posts: 1	We have a number of excel spreadsheets which contain links to other spreadsheets. In the future, we are planning on changing our directory structure which will cause problems for the cells that contain links to other spreadsheets. Is there a simple way to identify the cells which have links in them, the link itself, and a someway to change the links without having to change every linked cell in every spreadsheet?

Apr 22nd, 2002, 03:09 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	if it were me, with 10 files, I'd open them all up then save them to the new directory.... all the links would change automatically. My guess is you have a lot more than 10 though, right ?

Apr 22nd, 2002, 03:26 PM	#3
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Why dont you put the file path being linked to in A2 say, like C:MyFile2001myfile.xls then, use indirect function to link. For instance =INDIRECT(A2,T65) I think would pick out T65 from C:MyFile2001myfile.xls which is in A2. So next year, just put the new path in A2, and your whole spreadsheet will be updated. RET79

Apr 22nd, 2002, 03:38 PM	#4
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	I think what I am describing is parameterising file names. It's very effective, and rigid. Changing one cell with the file path updates the whole sheet or book. wicked RET79