handling links to external spreadsheets

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a large number of links to cells in an external spreadsheet
that look like the following, where different cells on the source
spreadsheet are referenced:

for example, contents of cell Z5 on the destination spreadsheet:
='http://dummy_address.com/teams/projects/[data_analysis.xls]sheet1'!
C3 (source location)

I would like to have the ability to easily change the path, filename
and sheetname for this large number of references while leaving the
target cell (C3 in the case above) the same. Is there some method
where I could put the path, filename and sheetname in cells on the
destination spreadsheet and then in each of the destination cells put
a formula that would assemble the complete string to reference teh
source cell?

Thanks
 
John said:
for example, contents of cell Z5 on the destination spreadsheet:
='http://dummy_address.com/teams/projects/
[data_analysis.xls]sheet1'!C3
(source location)

I would like to have the ability to easily change the path,
filename and sheetname for this large number of references while
leaving the target cell (C3 in the case above) the same. Is there
some method where I could put the path, filename and sheetname in
cells on the destination spreadsheet and then in each of the
destination cells put a formula that would assemble the complete
string to reference teh source cell?
Click to expand...

If you need the full path, these aren't open files. In that case,
there's no good option for handling this purely by formula. If the
path, filename and sheetname wouldn't change often, it may be
expedient to use something like option 1 in the following article from
the archives.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075
 
If you need the full path, these aren't open files. In that case,
there's no good option for handling this purely by formula. If the
path, filename and sheetname wouldn't change often, it may be
expedient to use something like option 1 in the following article from
the archives.

http://groups.google.com/group/microsoft.public.excel.worksheet.funct...
Click to expand...


Thank you for the pointer Harlan. After looking at the options I think
I'll just stay with what I've got.

PS - Thanks for the reminder of Frank's contributions to these groups.
 
Back
Top