Named Range in 3-D Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have need to insert a named range into a 3-D formula such as this

='C:\Work\[DataFile.xls]Sheet1'!$A$

replacing the column reference "A" with a named range (let's call it "NewColumn") that has a value for a new column reference, i.e. "B"

Logically, the new formula should look something like this

='C:\Work\[DataFile.xls]Sheet1'!$NewColumn$
o
=("='C:\Work\[DataFile.xls]Sheet1'!$" & NewColumn & "$1") {I have tried many variations without the intended result of the construction of the formula.

After entering the formula above, the correct text of the formula is displayed in the cell as a value, rather than the true value within the cell referenced by the cell address "$NewColumn$1" (or "$B$1"). Concatenating named ranges for column and row references have worked in the past to create the desired result, but I have not used this approach using a 3-D formula to another file before now

I would greatly appreciate any suggestions as to how to convert the newly concatenated text within the resulting formula to a true value and operational formula using a named range for a column reference

Ed
 
I have need to insert a named range into a 3-D formula such as this:

='C:\Work\[DataFile.xls]Sheet1'!$A$1

replacing the column reference "A" with a named range (let's call
it "NewColumn") that has a value for a new column reference, i.e. "B".
Click to expand...
...

This can't be done directly. If the other workbook would be open, you could use
the INDIRECT function.

=INDIRECT("'[DataFile.xls]Sheet1'!"&NewColumn&"1")

If the other workbook could be closed, then see

http://www.google.com/[email protected]
 
Thank you for your response and information; using the INDIRECT function will now allow the use of a named range in place of a column reference in my formulas

Since I have multiple files of considerable size that will need to be opened for INDIRECT to work appropriately, I will use INDIRECT in conjuntion with VB coding to achieve the final desired results. Without your response and information, I would have created an alternative solution achieving the same results, but at a cost of multiple hours of upfront work now, and additional minutes of time every fiscal week; all with the potential setbacks of human error. With INDIRECT and VBA, all needed values will be automatically & accurately retrieved and calculated within seconds

Ed
 
Thank you for your response and information; using the INDIRECT function will
now allow the use of a named range in place of a column reference in my
formulas.
Click to expand...

As long as the workbooks to which you're referring are open, yes. Otherwise, no.
Since I have multiple files of considerable size that will need to be opened
for INDIRECT to work appropriately, I will use INDIRECT in conjuntion with VB
coding to achieve the final desired results.
Click to expand...
...

If you're going to use VBA anyway, you'd be better off using it to enter literal
external link references in cell formulas than using it to enter formulas using
INDIRECT to construct such references.
 
Back
Top