display contents of cell in another workbook
 

Hi all

What am I missing

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Road Managers Package\[Tour Managers Spreadsheet.xls]Tour managers
Spreadsheet'!C111

Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet




=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")

Just displays the text of the formula and not the contents of the C111 CELL

LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)

Gives me
C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\


All Help Welcome

Stew

display contents of cell in another workbook
 

"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open.

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")

display contents of cell in another workbook
 

So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa

"FiluDlidu" wrote:

"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open.

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")

display contents of cell in another workbook
 

Your first example was a reference in itself and went to find the value of
your reference. Your second example was not a reference but a string.

Example:

If you type...
=A1
into cell B1, it will return whatever cell A1 contains;

If instead you type...
="A1"
into B1, then B1 will see that it needs to display a string;

Using indirect turns a string into a reference, so typing...
=indirect("A1")
into B1 will returns whatever cell A1 contains.

But as I mentioned in my previous post, INDIRECT doesn't work with
references to other workbooks when the workbooks in question are not open.

"stew" wrote:

So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa

"FiluDlidu" wrote:

"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open.

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")

display contents of cell in another workbook
 

Hi again, Stew.

Do a google search for morefunc - this a free addin that you can
download and it gives you several new functions in Excel, including
INDIRECT.EXT. This does allow you to get data from a closed workbook,
and you would use it like:

=INDIRECT.EXT( your_formula )

Hope this helps.

Pete

On Nov 7, 5:21*pm, stew wrote:
So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road *

* Accounting Package\Road Managers Package\[Tour Managers
* Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa



"FiluDlidu" wrote:
"stew" wrote:
Hi all
What am I missing
* ='C:\Users\Welcome\Stewarts Files\Road accounting program\Road *
* Accounting Package\Road Managers Package\[Tour Managers
* Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. *To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open..

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-*25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")- Hide quoted text -

- Show quoted text -

display contents of cell in another workbook
 

Hi Pete

Did It

Works A treat

"Wish me luck , as you wave me goodbye,here I go, on my way, Cheerio"

Thanks

Stew

"Pete_UK" wrote:

Hi again, Stew.

Do a google search for morefunc - this a free addin that you can
download and it gives you several new functions in Excel, including
INDIRECT.EXT. This does allow you to get data from a closed workbook,
and you would use it like:

=INDIRECT.EXT( your_formula )

Hope this helps.

Pete

On Nov 7, 5:21 pm, stew wrote:
So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road

Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa



"FiluDlidu" wrote:
"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open..

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-Â*25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")- Hide quoted text -

- Show quoted text -

display contents of cell in another workbook
 

Dear Pete

Small Glitch with the "indirect.ext" Function

The Closed remote work book that it refers to has to have been opened at
least once before being closed or else the indirect,ext returns a "name"
error, in this particular case. Could be to do with the
("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25) part of the
address



Best

Stew

"stew" wrote:

Hi Pete

Did It

Works A treat

"Wish me luck , as you wave me goodbye,here I go, on my way, Cheerio"

Thanks

Stew

"Pete_UK" wrote:

Hi again, Stew.

Do a google search for morefunc - this a free addin that you can
download and it gives you several new functions in Excel, including
INDIRECT.EXT. This does allow you to get data from a closed workbook,
and you would use it like:

=INDIRECT.EXT( your_formula )

Hope this helps.

Pete

On Nov 7, 5:21 pm, stew wrote:
So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road

Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa



"FiluDlidu" wrote:
"stew" wrote:
Hi all
What am I missing
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road
Accounting Package\Road Managers Package\[Tour Managers
Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open..

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-Â*25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")- Hide quoted text -

- Show quoted text -

display contents of cell in another workbook
 

Well, I don't use it myself - just passing on the information ...

Glad that it (almost) worked for you.

Pete

On Nov 8, 12:35*am, stew wrote:
Dear Pete

Small Glitch with the "indirect.ext" Function

The Closed remote work book that it refers to has to have been opened at
least once before being closed or else the indirect,ext returns a "name"
error, in this particular case. Could be to do with the
("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25) part of the
address

Best

Stew



"stew" wrote:
Hi Pete

Did It

Works A treat

"Wish me luck , as you wave me goodbye,here I go, on my way, Cheerio"

Thanks

Stew

"Pete_UK" wrote:

Hi again, Stew.

Do a google search for morefunc - this a free addin that you can
download and it gives you several new functions in Excel, including
INDIRECT.EXT. This does allow you to get data from a closed workbook,
and you would use it like:

=INDIRECT.EXT( your_formula )

Hope this helps.

Pete

On Nov 7, 5:21 pm, stew wrote:
So how does the First path work

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road *

* Accounting Package\Road Managers Package\[Tour Managers
* Spreadsheet.xls] Tour managers Spreadsheet'!C111

and gives the result

So is this the only way to do this ,do you think.

Best

Stewa

"FiluDlidu" wrote:
"stew" wrote:
Hi all
What am I missing
* ='C:\Users\Welcome\Stewarts Files\Road accounting program\Road *
* Accounting Package\Road Managers Package\[Tour Managers
* Spreadsheet.xls] Tour managers Spreadsheet'!C111
Displays the contents of C111 IN WORK BOOK "Tour Managers
Spreadsheet.xls"tour managers spreadsheet

I'm guessing this is what you want for a result, is it not?

=("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)
&"Road Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")
Just displays the text of the formula and not the contents of the C111 CELL

Your formula was indeed designed to display a path and a cell reference at
the end. *To have the result of Excel looking down this path and read the
value of the given reference, you would need to include your formula inside
the INDIRECT function, but unfortunately, INDIRECT doesn't work with
references to an external workbook that is not open, so you will get an error
for this formula every time "Tour Managers Spreadsheet.xls" is not open..

=INDIRECT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-**25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!C111")- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -