Alternative to Indirect Function
Hello List,
Is there another method to reference a cell in an external workbook other than the function indirect? The workbooks that I need to reference will reside on a server and will be closed when a refresh is made to the consolidating workbook. Assume the following: * Book1.xls is located at: \\Server\ProductType\ * Book1 Contains Sheet named Profile * Sheet Profile has a cell $A$1="Needed Output" Book2.xls is a consolidating workbook that references many external workbooks. * Book2 Contains a sheet named "Setup" * In Setup three columns exist where the user types the path of a specified workbook (\\server\productType\), the file name (book1.xls) and the name of the profile sheet (Profile). A fourth column concantenates the column and places them int the appropriate form (lets' call it Cell D2 which equals '\\Server\ProductType\[Book1.xls]Profile'!) * Cell b1 of Sheet2 needs to reference the path from the Setup sheet and reference cell $A$1 (of book1) I've tried =Profile!D2 & $A$1 but it doesn't work.. Basically I need the above to be interpretted as ='\\Server\ProductType\[Book1.xls]Profile'!$A$1 Any Suggestions would be greatly appreciated. Jeff |
Alternative to Indirect Function
I think there is no solution using only worksheet
functions. The problem is that you need to work references as text and the only way i know to achieve it is through the INDIRECT. Curious to see other posts. Vasco -----Original Message----- Hello List, Is there another method to reference a cell in an external workbook other than the function indirect? The workbooks that I need to reference will reside on a server and will be closed when a refresh is made to the consolidating workbook. Assume the following: * Book1.xls is located at: \\Server\ProductType\ * Book1 Contains Sheet named Profile * Sheet Profile has a cell $A$1="Needed Output" Book2.xls is a consolidating workbook that references many external workbooks. * Book2 Contains a sheet named "Setup" * In Setup three columns exist where the user types the path of a specified workbook (\\server\productType\), the file name (book1.xls) and the name of the profile sheet (Profile). A fourth column concantenates the column and places them int the appropriate form (lets' call it Cell D2 which equals '\\Server\ProductType\[Book1.xls] Profile'!) * Cell b1 of Sheet2 needs to reference the path from the Setup sheet and reference cell $A$1 (of book1) I've tried =Profile!D2 & $A$1 but it doesn't work.. Basically I need the above to be interpretted as ='\\Server\ProductType\[Book1.xls]Profile'!$A$1 Any Suggestions would be greatly appreciated. Jeff . |
Alternative to Indirect Function
Hi Jeff,
Indirect is the only worksheet solution other than a hard coded Excel link. (='\\Server\ProductType\[Book1.xls]Profile'!$A$1) Since your user is inputting the path, you could use VBA to get the path from the user with a userform or inputbox. Construct the hard coded Excel link and paste it into the cell as a formula, all with VBA. -- John johnf202 at hotmail dot com "Jeff Coleman" wrote in message ... Hello List, Is there another method to reference a cell in an external workbook other than the function indirect? The workbooks that I need to reference will reside on a server and will be closed when a refresh is made to the consolidating workbook. Assume the following: * Book1.xls is located at: \\Server\ProductType\ * Book1 Contains Sheet named Profile * Sheet Profile has a cell $A$1="Needed Output" Book2.xls is a consolidating workbook that references many external workbooks. * Book2 Contains a sheet named "Setup" * In Setup three columns exist where the user types the path of a specified workbook (\\server\productType\), the file name (book1.xls) and the name of the profile sheet (Profile). A fourth column concantenates the column and places them int the appropriate form (lets' call it Cell D2 which equals '\\Server\ProductType\[Book1.xls]Profile'!) * Cell b1 of Sheet2 needs to reference the path from the Setup sheet and reference cell $A$1 (of book1) I've tried =Profile!D2 & $A$1 but it doesn't work.. Basically I need the above to be interpretted as ='\\Server\ProductType\[Book1.xls]Profile'!$A$1 Any Suggestions would be greatly appreciated. Jeff |
All times are GMT +1. The time now is 03:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com