gpie wrote...
I have tried using PULL from Harlan Grove's posts to workaround this
but am coming up with #VALUE errors.
Here's what I have:
Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
So this col E cell would evaluate to "_5230" ?
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
_5230Rows won't be interpretted as a reference into an external
workbook. You need to include the drive/directory path and filename.
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230
If _5230Rows refers to the first column of _5230, use one formula
rather than two - eliminate the col F formulas and use
=VLOOKUP($B$3,INDIRECT($Er),COLUMN(G$6))
However, same comment as above about the need for drive/directory path
and filename.
I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
however it returns #REF!
Are you sure you don't mean you thought
INDEX( INDIRECT( $Er ), $Fr, COLUMN(G$6))
should work?
I need a solution to replace INDIRECT so I do not have to have both
workbooks open together. The named ranges are static, but reference
external workbooks.
What did your pull formulas that returned #VALUE! *REALLY* look like?
|