| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Big Cell Contents - I don't want them to display | Excel Discussion (Misc queries) | |||
| Cell contents don't display everything entered? | Excel Discussion (Misc queries) | |||
| display contents of cell | Excel Discussion (Misc queries) | |||
| How do I display contents of an adjoining cell? | Excel Worksheet Functions | |||
| Display actual contents of cell | Excel Discussion (Misc queries) | |||
Linear Mode
