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

[Cat]

Cat
Nov 10, 9:48 am show options

Newsgroups: microsoft.public.excel.programming
From: "Cat" <[email protected]> - Find messages by this author
Date: 10 Nov 2005 07:48:00 -0800
Subject: Linked Workbooks - How to Allow for Growth in source books?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

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!

 

[Bernie Deitrick]

Cat,

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

 

[Earl Kiosterud]

Cat,

Instead of cell links, consider using Data - Import External Data - Import
Data. It need be set up only once, and then you can right-click the
destination range and click "Refresh" any time the source may have changed.
Totals and other stuff under the data in the target get moved down
automatically. Right-click and use "Data range properties" for lots of
options on how the data is to be imported and the sheet handled during
Refresh.

When setting it up (Data - Import External Data - Import Data), you need not
necessarily build a data source. In the Select Data Source dialog box, just
navigate directly to the source workbook.

A macro can do the refresh, and requires only one statement (Refresh). It
could be in an event proc, like any time the destination sheet is selected.

Earl Kiosterud
www.smokeylake.com

 

[Harlan Grove]

Cat wrote...
....

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.

FTHOI, someone might as well provide a formula answer.

One formula techniques to do this involves some ancillary formulas and
manual steps in the process. If the filenames follow a set pattern,
e.g., West2005Q1.xls, East2005Q2.xls for 1st quarter 2005,
West2005Q2.xls, East2005Q1.xls for 2nd quarter 2005, etc., you could
use one entry cells for the drive/directory path in which your copies
of the files are stored (I'll use cell A1 in a worksheet named P),
another entry cell for year and quarter (I'll use cell A2 in worksheet
P), and enter a list of the repeating portions of the filenames (in my
example, West and East; I'll put the topmost such entry in cell A4 in
worksheet P with the other entries below it). Add an error value like
#NULL! in the cell immediately below the last partial filename entry in
col A.

Then enter the following formulas.

P!B4:
1

P!B5:
="=COUNTA('"&$A$1&"\["&A4&$A$2&".xls]DesiredWorksheet'!A:A)+B"&ROW(B4)

Select P!B5 and fill down to match the entries in col A (or with P!B5
the active cell, double click the fill handle). From P!B5 down this
will produce text that looks like formulas.

Add another worksheet to pull in the values from the other workbooks.
In cell A1 of that worksheet enter the formula

Other!A1:
="='"&P!$A$1&"\["&LOOKUP(ROW(),P!$B$4:$B$103,P!$A$4:$A$103)&P!$A$2
&".xls]DesiredWorksheet'!A"&(ROW()+1-LOOKUP(ROW(),P!$B$4:$B$103))

This will produce more text that looks like formulas. Note that I've
assumed there'll be 100 different workbooks, P!A4:A103 holding the
partial filenames. Save this workbook as a template (not necessarily as
an .XLT file, but as a file you'll load and resave each period using a
different filename).

When you need to extract the values from a new set of workbooks, open
this template, save it under a different filename, then activate
worksheet P, select the cells in col B beginning with cell B4, Edit >
Copy, Edit > Paste Special as values on top of this range, then Edit >
Replace replacing = with = (which appears to be a do-nothing operation,
but it effectively converts text constants into formulas). This should
replace the text with numbers that show the row number for the
cumulative result corresponding to the first row from the workbook that
appears in col A.

Now activate the other worksheet, fill A1 down until the formula
returns an error value. Select the col A range of formulas, Edit >
Copy, Edit > Paste Special as values on top of this range, then Edit >
Replace replacing = with =. This should convert the text into literal
external link references into the various other workbooks with no gaps
between data from different workbooks, and all data in each workbook
included.

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

Columns or rows?