Reference to another workbook

  • Thread starter Thread starter Nenad Markovic
  • Start date Start date
N

Nenad Markovic

Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



Nesha
 
You should look up INDIRECT. Your formula would look something like:

=INDIRECT("'[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")

You need to check whether it works with a closed source workbook; I have
a nagging suspicion that it doesn't.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Selec the cells with the formulas and do

Edit=>Copy

then do
Edit=>Paste Special and select values.
 
No, it doesn't work with a closed workbook.

--
Regards,
Tom Ogilvy

Jon Peltier said:
You should look up INDIRECT. Your formula would look something like:

=INDIRECT("'[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")

You need to check whether it works with a closed source workbook; I have
a nagging suspicion that it doesn't.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Nenad said:
Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



Nesha
Click to expand...
Click to expand...
 
Nevermind, misread your question.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Selec the cells with the formulas and do

Edit=>Copy

then do
Edit=>Paste Special and select values.

--
Regards,
Tom Ogilvy

Nenad Markovic said:
Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



Nesha
Click to expand...
Click to expand...
 
Thank you,



I've tried with INDIRECT function myself but always got REF message. It
doesn't work with closed source workbook but, although it is no very
comfortable to work with some 60 opened workbooks, it saved me a lot of
time.



I still don't understand why doesn't it work with path inserted in the
beginning of the first string, something like



=INDIRECT("'C:\....\....\[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")



since "'C:\....\....\[OP " is a string just like "'[OP ".



Thanks again,

Nesha

Jon Peltier said:
You should look up INDIRECT. Your formula would look something like:

=INDIRECT("'[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")

You need to check whether it works with a closed source workbook; I have
a nagging suspicion that it doesn't.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Nenad said:
Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



Nesha
Click to expand...
Click to expand...
 
Thanks anyway

Tom Ogilvy said:
Selec the cells with the formulas and do

Edit=>Copy

then do
Edit=>Paste Special and select values.

--
Regards,
Tom Ogilvy

Nenad Markovic said:
Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



Nesha
Click to expand...
Click to expand...
 
I still don't understand why doesn't it work with path inserted in the
beginning of the first string
Click to expand...

Read the help file on Indirect. It states it doesn't work with closed
workbooks.

If you need a reason, I would assume that based on the way Excel handles
external links and calculation, the Microsoft developers have chosen not to
write the (assumed extensive) code to support it.

--
Regards,
Tom Ogilvy


Nenad Markovic said:
Thank you,



I've tried with INDIRECT function myself but always got REF message. It
doesn't work with closed source workbook but, although it is no very
comfortable to work with some 60 opened workbooks, it saved me a lot of
time.



I still don't understand why doesn't it work with path inserted in the
beginning of the first string, something like



=INDIRECT("'C:\....\....\[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")



since "'C:\....\....\[OP " is a string just like "'[OP ".



Thanks again,

Nesha

Jon Peltier said:
You should look up INDIRECT. Your formula would look something like:

=INDIRECT("'[OP "&A1&" ST "&B1&".xls]Sheet1'!$D$6")

You need to check whether it works with a closed source workbook; I have
a nagging suspicion that it doesn't.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Nenad said:
Does anybody know how can I replace part of the reference with the value
from the cell? For instance, in the reference to another workbook



='[OP 26 ST 42.xls]Sheet1'!$D$6



that references to the cell D6 in Sheet1 in workbook OP 26 ST 42.xls i want
to replace 26 and 42 with values form, say cells A1 and B1 in current sheet
of a current workbook. If the values are A1= 99 and B1=85 the reference
should now look like this:



='[OP 99 ST 85.xls]Sheet1'!$D$6



referencing to the presumably existing workbook OP 99 ST 85.xls.





Thanks in advance



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