Thanks for your response, Harlan. I think I am misunderstanding how
your PULL function works. I have answered your questions below.
Thanks again for your help!

Harlan Grove wrote:
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" ?

Yes.


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.


In this workbook, _5230Rows is defined as ='[Detail Account
Budgets.xls]5230'!$B$51:$B$70

Detail Account Budget.xls is the external workbook. So I am not
referring to a name in an external workbook, the name refers to the
external workbook. Should I set it up the opposite way?

--snip--


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?


No, that formula does work, as long as I have the other workbook open.
If I hard-code the formula as INDEX(_5230,_5230Rows,COLUMN(G$6)) it
works even with the other book closed, I'm not really sure why $Er
needs the INDIRECT to work. The cell looks like _5230

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?

Here is one example, using the INDEX function above

=INDEX(PULL($E22),$F22,COLUMN(G$6))