"Changeable" External References

  • Thread starter Thread starter SVT
  • Start date Start date
S

SVT

Is there a way to change the file, sheet, or cell that an
external reference is pointing to via linking to other
cells?

For example, if I want to link to 'C:\Example\[Example.xls]
ABC123!$A$1 and this formula is in cell B1 of my
destination worksheet.

Can I have the file name "Example.xls" in cell A1 of my
destination worksheet, the sheet name "ABC123" in cell A2
of my destination worksheet, and the cell reference $A$1
in cell A3 of my destination worksheet.

This way I can make duplicates of my template worksheet,
then just change the file, sheet, and cells (if I wanted
to) and it will update all of my external reference
linking.

Or, can I do this with a macro and have an "update" button
or something?

Otherwise I am going to have to manually link many, many,
many files!!
 
You could have
=INDIRECT("'C:\EXample\["&A1&]"&A2&"'!"&A3)
in B1.

One big problem here, the workbook being referred to must be open, otherwise
it doesn't work.
 
Thanks for responding, that is intersting, I didn't even
know you could do that. However, it won't work for my
application since the file it is referring to will not be
open at the same time - at least not all of the time.
-----Original Message-----
You could have
=INDIRECT("'C:\EXample\["&A1&]"&A2&"'!"&A3)
in B1.

One big problem here, the workbook being referred to must be open, otherwise
it doesn't work.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


SVT said:
Is there a way to change the file, sheet, or cell that an
external reference is pointing to via linking to other
cells?

For example, if I want to link to 'C:\Example\[Example.xls]
ABC123!$A$1 and this formula is in cell B1 of my
destination worksheet.

Can I have the file name "Example.xls" in cell A1 of my
destination worksheet, the sheet name "ABC123" in cell A2
of my destination worksheet, and the cell reference $A$1
in cell A3 of my destination worksheet.

This way I can make duplicates of my template worksheet,
then just change the file, sheet, and cells (if I wanted
to) and it will update all of my external reference
linking.

Or, can I do this with a macro and have an "update" button
or something?

Otherwise I am going to have to manually link many, many,
many files!!
Click to expand...


.
Click to expand...
 
Thought that might be the case.

Here is another suggestion, although I admit to not having tried this
myself, just remember the suggestion from Harlan Grove.
http://google.com/[email protected]


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


SVT said:
Thanks for responding, that is intersting, I didn't even
know you could do that. However, it won't work for my
application since the file it is referring to will not be
open at the same time - at least not all of the time.
-----Original Message-----
You could have
=INDIRECT("'C:\EXample\["&A1&]"&A2&"'!"&A3)
in B1.

One big problem here, the workbook being referred to must be open, otherwise
it doesn't work.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


SVT said:
Is there a way to change the file, sheet, or cell that an
external reference is pointing to via linking to other
cells?

For example, if I want to link to 'C:\Example\[Example.xls]
ABC123!$A$1 and this formula is in cell B1 of my
destination worksheet.

Can I have the file name "Example.xls" in cell A1 of my
destination worksheet, the sheet name "ABC123" in cell A2
of my destination worksheet, and the cell reference $A$1
in cell A3 of my destination worksheet.

This way I can make duplicates of my template worksheet,
then just change the file, sheet, and cells (if I wanted
to) and it will update all of my external reference
linking.

Or, can I do this with a macro and have an "update" button
or something?

Otherwise I am going to have to manually link many, many,
many files!!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top