Updating Indirect Reference(s)

[Guest]

I have a workbook with indirect references for vlookups into other workbooks.
The indirect reference is not recognized as a link when I open the
spreadsheet and does not update. I must open the referenced spreadsheets for
the vlookup to work. This is monthly budget data, so by December I'll be
opening 72 workbooks (and Enabling macros for each one). Any way to have the
indirect reference be seen as a link?

 

[Harlan Grove]

I have a workbook with indirect references for vlookups into other workbooks.
The indirect reference is not recognized as a link when I open the
spreadsheet and does not update. I must open the referenced spreadsheets for
the vlookup to work. This is monthly budget data, so by December I'll be
opening 72 workbooks (and Enabling macros for each one). Any way to have the
indirect reference be seen as a link?

If you mean you're using the INDIRECT function to refer to ranges in
other workbooks, then there's no way to make them work unless *ALL*
the other files are open. Why are you using indirect links? It may be
easier to use, er, direct external reference links which wouldn't
require that the other workbooks be open.

Give a few examples of these formulas. There may be workarounds.

 

[Gord Dibben]

Excel's INDIRECT function must have the referenced workbook open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =INDIRECT.EXT function which does not require the workbook to be
open.


Gord Dibben MS Excel MVP

On Wed, 7 Feb 2007 12:59:02 -0800, Sunrise TG <Sunrise

 

[Guest]

I'll include an example, maybe someone has a better way. I didn't use direct
reference because I'm looking up the same actual vs. budget data from 8
different dept. workbooks with similar naming conventions (dept name in
filename), changing each month by the date referenced in the file names. I
think you'll see by the indirect reference. I built the reference from
several cells that contain common and variable info. In total there are 144
similar but different indirect references on each monthly tab.

=VLOOKUP(B6,INDIRECT(J4),3,FALSE)
where
B6 is a known subtotal row header
and J4 contains:

'F:\Financials\2007\1-Jan\Dept_Income_Statements\[215_TG_DEPT_2007-01-31.xls]Sheet1'!$B$2:$AA$100

My only work around so far is to keep a clean template tab and after the
data is loaded each month, copy/paste values (for historical reference).
Then I just need to open the 8 current month workbooks. Alternately, I can
just enter the data each month, but that's not as much fun! Thanks!