Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very complex IF function if not an alternative function of crossselection | Excel Worksheet Functions | |||
Indirect alternative | Excel Discussion (Misc queries) | |||
Alternative to Indirect | Excel Worksheet Functions | |||
Alternative for IF function | Excel Discussion (Misc queries) | |||
Excel If function alternative | Excel Worksheet Functions |