Multiple workbooks with paste special?

[sumichung]

Help! I don't know what the best way to do this, I've been surfing
these postings on various sites for the past few days as well as bought
a book. And I still not sure what I should do.

I have 6 individual workbooks for each rep that I want to consolidate
into one master workbook, either with one worksheet or 6 worksheet.
This is for sales progress, so not necessarily looking for a sum total.
I want to have the ease of looking at one master workbook instead of
opening each of the 6 workbooks. Each workbook has same column header
but may have different number of rows depending on their sales
progress. Some may have 10 rows with data, some may have 50 rows.

I figured out how to do the Paste Special. With this, I think the best
would be with one master workbook and 6 worksheet, since I don't know
how many rows each individual workbook will contain. The only thing
is, there is alot of 0's because it already has the links. Anyway
around this? Cool thing is that info is updated even when the master
is closed.

Is this the best way or is there another way to do this? I just want
to compile a master with all information for each rep, and each
workbook has the simple summation formula. Is data consolidation the
best for this? Also, I am not that advanced so I won't be able to do
any of the programming/VB yet!

Thank you in advance!

sumi

Thanks!

 

[Arvi Laanemets]

Hi

=IF(YourLink="","",YourLink)
, and you can have as many rows in master workbook ready as you care. But
beware, more formulas means a slower workbook.

Another way is to read all data in through ODBC queries when workbook is
opened. Opening the workbook takes somewhat more time, but there will be no
links to source files, so workbook itself will be fast. Such solution is
especially useful when you use additional formulas in master workbook to get
some summary data or reports.

To use ODBC queries to get data from other Excel files you have to define
data tables in every source workbook as a fixed named range (simple range
reference without any OFFSET's or INDEX'es etc. involved). And you must have
Analysis Toolpack Add-in activated (At least in Excel2000 - otherwise the
queries will work only, when source workbooks are opened. Don't ask me
why!). Now select a sheet you want the data from some source table read
into, from Data menu start ODBC query wizard (Get External Data>New Database
Query), select Excel ODBC driver, determine the source workbook as database
and named range as table, etc. In Data Range Properties you can determine,
when query is refreshed - my advice is to refresh queries on open. You can
also determine a time interval for query to be refreshed repeatedly, but I
myself prefer to do it manually or through a VBA procedure, whn there is a
need for that.

 

[sumichung]

Thanks, I"ll give this a try!