Linked Workbooks - How to Allow for Growth in source books?

[Cat]

I have several workbooks that I have linked together into one. I would
like to be able to show them in the destination workbook in a long
list, without space left in between each subset of data. The issue is,
when I add a new row in the source workbook, it does not get picked up
in the destination workbook. OR, if I select beyond the actual data
subset in the source (blank rows) to allow for the possibility of
additional rows being added, then these rows get all filled with zeros
when copied into the destination - a result I cannot allow since zero
is a valid value in some columns and this skews the data analysis being
done.

Is there a way to get the additional new columns into the destination
workbook without having to initially set it up with blank rows?

Suggestions?

Thanks!

Cat

 

[Bernie Deitrick]

Cat,

No need to post multiple times - either cross post (one post to multiple groups, all at once) or
just post to the most likely-looking group. Most of the big-guns follow most of the popular groups
anyway.

Here's my previous answer:

1) Use a macro, one that combines the data sets just prior to processing the data. Usually, very
efficient.
2) (this would be my preference) - Don't use separate files - use one file, a one-sheet database,
with an additional field to indicate which file it would have gone in. Then use data filters to
show subsets of the data when you want to see the individual files.

HTH,
Bernie
MS Excel MVP

 

[Cat]

My issue was not in being able to identify which rows are from what
source book, but specifically allowing the master workbook to get new
rows entered in the source. For example, source workbook 1 has the
following:
ColA ColB ColC
XXX BBB 999

Source workbook 2 has following:
ColA ColB ColC
PPP UUU 111

So the master has this view:
ColA ColB ColC
XXX BBB 999
PPP UUU 111

When Source Workbook 1 gets a new row, like -

ColA ColB ColC
YYY NEW 989

I need the master to get that new row, and using Copy, Special Paste,
Paste Link, the only way I know to get it is to initially copy more
than the row that is populated, and this means having a master workbook
with a lot of padded empty rows to allow for the additional rows in the
source workbooks.

Hopefully this makes it clearer.

Thanks!