"Changeable" External References

[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!!

 

[Bob Phillips]

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.

 

[SVT]

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

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!!

.

 

[Bob Phillips]

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

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

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!!

.