Combining data from many files

  • Thread starter Thread starter cd
  • Start date Start date
C

cd

I have approx 1,500 similar spreadsheets that summarize
financial activity. Each file summmarizes the activity
for 1 account. How can I pull certain data from each
spreadsheet into one main spreadsheet without having to
enter 1,500 cell references or lookups. (i.e. can the file
name in a cell reference be a variable?) There are 1,500
different values for the file name in this
formula "=VLOOKUP(B5,'[00204198.xls]DVR EXCESS CHECK'!
$C$1:$H$63,6,FALSE)" Can I change "00204198.xls" to be a
variable that is set to a list of account numbers?
 
Indirect won't work with closed workbooks but you use use find/replace in
the formulas to change the workbook name
 
This won't directly solve your problem, but I wonder if
you would have an easier time managing all this data if
you used Microsoft Access. There is a way to keep all your
accounts in one table and have each account have a sub-
table that contains the more specific information. It's
easy to run queries from them to combine certain
information, and make a new table from that query.

For instance, you could have one table titled "all
accounts" that lists the name, account number and address
for your client. If you made a sub-table, there will be a
+ sign that appears in the first column of the "all
accounts" table. If you click the +, a sub-table opens up
for all the transactions from that account number (date of
purchase, item purchased, units, etc.). You could then run
a query to find all clients in California, for instance,
or all accounts that purchased a tennis racquet, and put
all of those clients in a new table.

Hope this helps.
 
If the only delta between the file names is the number, and you have a list of the numbers, then
try the following:-

Open a blank sheet and one of your files - Arrange the windows and type = in A1 in your blank
sheet and then using the mouse click on the cell you want in the other file and hit enter. Now
close the file with all the data in. Your blank file should have a formula like the following in
it:-

='C:\4 Temp\Test\[Test1.xls]Sheet 1'!$D$6

Copy this to cell C1 as well. In my case, the only changing element was the 1, which I had as
being 2,3,4,5,6 etc

Now trim the formula in A1 to just before the file number, eg:-

='C:\4 Temp\Test\[Test

and trim the formula in C1 from just after that number eg:-

..xls]Sheet 1'!$D$6

Now copy both these down for 1500 rows.

Now, in cell B1 put 1 (or whatever your numbers are), and in D1 you want =A1&B1&C1 and copy down
for 1500 rows.

Select Col D and copy and then paste special as values, and you can now delete Cols A / B / C

Select all the data in A and do Edit / Replace, replacing = with =

This should make all the formulas live for you
 
Back
Top