Reference to another workbook

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
 
J

Jon Peltier

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/
_______
 
T

Tom Ogilvy

Selec the cells with the formulas and do

Edit=>Copy

then do
Edit=>Paste Special and select values.
 
T

Tom Ogilvy

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...
 
T

Tom Ogilvy

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...
 
N

Nenad Markovic

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...
 
N

Nenad Markovic

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...
 
T

Tom Ogilvy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arrays and scattered cell values or another way? 16
How to create absolue path link to another workbook (Excel 2003) 1
How to create absolute link to cell in another workbook (Excel 2003) 1
Cell values to an array, then array transpose to another workbook 8
Code to format cells on several worksheets 2
Can I use the content of a cell as part of filereference in other 5
VBA for summing buys and sells 0
Copying selected rows to a new workbook 0

Top