Dynamic Range from External Reference

  • Thread starter Thread starter plano
  • Start date Start date
P

plano

I know how to create dynamic ranges for a range in the
current workbook for use in a pivot table. Saves a ton of
time.

I tried to do the same thing with an external workbook but
with no sucess.

Is there any way to create a pivot in workbook A that
dynamically updates with the data in workbook B? Workbook
B data will change daily (ie. the amount of rows will
change.)

Any help appreciated. Thanks!
 
plano,

There was a similar query recently. I had a go using consolidation
(non-starter), then a pivot table with multiple consolidation ranges (x 2,
one from each of 2 external wbs). At the time, I recall it was clunky.
Flagged it up but got no feedback.

Just now, fired up XL & opened all 3 files at once. Doing anything with the
table gave an error. Then I refreshed the table & it worked OK. Then saved
all 3 & closed XL. Ran it again & opened the 3, the table worked fine.
Closed down all 3. Opened the file with just the table, worked fine.

Further investigation, it looks like the table must be refreshed for changes
in the source book(s) to register. But the crux seems to be that the source
book(s) must be open for refresh to work (else you get "reference is not
valid ; cannot open source file"). If this *is* the case, it would probably
defeat your object. NB - I only tried with dynamic named ranges, not regular
ones.

Rgds,
Andy
 
Back
Top