Formula referring to a dynamic range in a different workbook

[Guest]

I've got two workbooks.

One has ranges which will grow as time passes. Because of this, I've set up
dynamic ranges.

The other one is a dashboard, which needs to VLOOKUP the contents of the
dynamic ranges in the other workbook.

It works if both workbooks are open, but if the one containing the raw data
is closed, the dashboard simply shows #VALUE!

Any ideas how I can get around this?

(Excel 2002)

Cheers,

Tom.

 

[Guest]

That's one of the shortcomings of VLOOKUP() using a table in another
workbook; both books have to be open to get updated data. I'm not certain
there's a good work-around for it.

One way (which almost defeats the effort you've gone to in setting up the
dynamic range in that other workbook) would be to have a sheet in the main
workbook that has direct cell links to the cells in that other workbook and
set our VLOOKUP() formulas to check those cells. But this takes either
manual intervention to update the links to make sure that all of the used
cells in the named range are linked to in the first workbook, or it takes an
overkill setup where you set the links to the cells in the table along with
more links below/to right of the table to allow for growth.

Potentially you could set up some code in the workbook that has your dynamic
range in it to update those links in the primary book as data is added to the
dynamic range (it would have to open the other workbook and set up added
links).

Another alternative would be to have the dashboard containing workbook check
to see if the workbook with the dynamic range in it is open, and if not, then
open it up so that the dashboard works properly. It could have code in the
_BeforeClose event to also close that other workbook when it was closed.
Someone else may have a better solution to this than what I've suggested.

 

[Guest]

Thank you.

I'll not tick 'answered' just yet because I really hope somebody has a more
attractive answer, but thank you all the same!

Tom.

 

[Don Guillett]

How about a defined name in the destination workbook that refers to the
source workbook.

 

[Guest]

Wow.

That's a very good idea.

I'll give it a try.

Cheers,

Tom.

 

[Guest]

Sorry. Works when the source sheet is open but not closed.

I suspect it's because with full paths included, the refs go beyond the 255
character limit for a range name, so I tried it with the source sheet open
and then closed it in the hope that the paths would update.

Will see if I can arrange some space closer to the root to allow shorter
path lengths...

 

[Guest]

Nope.

Defining the named range as an external sheet has the same effect.

Good try, though!

mr tom.