I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks

Any solution I can think of, other than VBA coding which would probably take
longer to coordinate between us than what I'm about to suggest will, will
involve some manual labor.

Assuming that your worksheet in each of the 70 books has the same name, and
that the layout on each of those sheets is the same, try this:

Start by making sure that all 70 of the other .xls files are in the same
folder.

Open up the main workbook where you want to consolidate the data along with
any one of the 70 other workbooks.
In each cell in the main workbook where you want information from the
other(s), type an equal symbol (=) and then go to the other open workbook and
click on the cell that has the data you want. Press [Enter]. Repeat for
each of the 15 data items from that one other book.

Close that other book! What this does is slightly change the formulas in
your cells in the main workbook to include the path to the folder that the 70
workbooks are in.

Check the formulas in the main workbook - they should have a $ sign in front
of both the column letter and row number for each cell referenced in the one
now closed. They should look something like this:
='C:\Documents and Settings\All
Users\Documents\[LooperData2.xls]LooperData'!$B$392
with the path to the file, the [filename.xls]! and the $c$r reference in the
other workbook.

Copy the formulas down for 69 rows (or across for 69 columns depending on
how you set things up)- so you now have something to work with for the other
69 files.

Now comes the hard part - the manual labor. Working with one set of data
for each of the other 69 workbooks, you can use Edit | Replace to change the
name of the workbook referenced in the formulas for that workbook.

Be sure you limit the changes made to the current selection or you'll keep
changing all the workbook names in all the cells each time, making no
progress at all. Since the path to the files is the same, and (if) the cells
were all located in the same places/sheets (and sheet names were the same)
then the only thing that needs to be changes is the workbook name, that part
within the square brackets, so you could then Edit | Replace
Find: [workbook1.xls]
Replace: [workbook2.xls]

Then repeat for each of the others, just changing the value in the Replace
parameter.

One thing about all of this - if you change the information in any of the 70
other workbooks, those changes will be reflected in this one also. If that
is undesirable, after you've finished making all of those changes, select all
of the cells with the formulas in them and first do Edit | Copy, then without
unselecting the group, use Edit | Paste Special and choose the [Values]
option. That will replace the formulas with the actual values just as if
you'd cut and pasted from each of the 70 worksheets - and changes in the
other workbooks will not affect the contents of the main one.

Sorry I couldn't offer you an easier solution. Given the 70 names of the
other workbooks, the sheet name(s) with the information on them and the
locations of the 15 pieces of data, a short macro could have been written to
do all that work in fairly short order.



"aggie mom in dallas" wrote:

I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks

Thank you!
I might be able to make this work
One folder does contain the 70
all 70 are named exaclty the same - except for unique customer number
so, let's try!!
Thanks again.

"JLatham" wrote:

Any solution I can think of, other than VBA coding which would probably take
longer to coordinate between us than what I'm about to suggest will, will
involve some manual labor.

Assuming that your worksheet in each of the 70 books has the same name, and
that the layout on each of those sheets is the same, try this:

Start by making sure that all 70 of the other .xls files are in the same
folder.

Open up the main workbook where you want to consolidate the data along with
any one of the 70 other workbooks.
In each cell in the main workbook where you want information from the
other(s), type an equal symbol (=) and then go to the other open workbook and
click on the cell that has the data you want. Press [Enter]. Repeat for
each of the 15 data items from that one other book.

Close that other book! What this does is slightly change the formulas in
your cells in the main workbook to include the path to the folder that the 70
workbooks are in.

Check the formulas in the main workbook - they should have a $ sign in front
of both the column letter and row number for each cell referenced in the one
now closed. They should look something like this:
='C:\Documents and Settings\All
Users\Documents\[LooperData2.xls]LooperData'!$B$392
with the path to the file, the [filename.xls]! and the $c$r reference in the
other workbook.

Copy the formulas down for 69 rows (or across for 69 columns depending on
how you set things up)- so you now have something to work with for the other
69 files.

Now comes the hard part - the manual labor. Working with one set of data
for each of the other 69 workbooks, you can use Edit | Replace to change the
name of the workbook referenced in the formulas for that workbook.

Be sure you limit the changes made to the current selection or you'll keep
changing all the workbook names in all the cells each time, making no
progress at all. Since the path to the files is the same, and (if) the cells
were all located in the same places/sheets (and sheet names were the same)
then the only thing that needs to be changes is the workbook name, that part
within the square brackets, so you could then Edit | Replace
Find: [workbook1.xls]
Replace: [workbook2.xls]

Then repeat for each of the others, just changing the value in the Replace
parameter.

One thing about all of this - if you change the information in any of the 70
other workbooks, those changes will be reflected in this one also. If that
is undesirable, after you've finished making all of those changes, select all
of the cells with the formulas in them and first do Edit | Copy, then without
unselecting the group, use Edit | Paste Special and choose the [Values]
option. That will replace the formulas with the actual values just as if
you'd cut and pasted from each of the 70 worksheets - and changes in the
other workbooks will not affect the contents of the main one.

Sorry I couldn't offer you an easier solution. Given the 70 names of the
other workbooks, the sheet name(s) with the information on them and the
locations of the 15 pieces of data, a short macro could have been written to
do all that work in fairly short order.



"aggie mom in dallas" wrote:

I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks

Try

http://www.rondebruin.nl/summary2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"aggie mom in dallas" wrote in message
...
I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks

Take a look at what Ron de Bruin has offered also, he may save you a lot of
work.

"aggie mom in dallas" wrote:

Thank you!
I might be able to make this work
One folder does contain the 70
all 70 are named exaclty the same - except for unique customer number
so, let's try!!
Thanks again.

"JLatham" wrote:

Any solution I can think of, other than VBA coding which would probably take
longer to coordinate between us than what I'm about to suggest will, will
involve some manual labor.

Assuming that your worksheet in each of the 70 books has the same name, and
that the layout on each of those sheets is the same, try this:

Start by making sure that all 70 of the other .xls files are in the same
folder.

Open up the main workbook where you want to consolidate the data along with
any one of the 70 other workbooks.
In each cell in the main workbook where you want information from the
other(s), type an equal symbol (=) and then go to the other open workbook and
click on the cell that has the data you want. Press [Enter]. Repeat for
each of the 15 data items from that one other book.

Close that other book! What this does is slightly change the formulas in
your cells in the main workbook to include the path to the folder that the 70
workbooks are in.

Check the formulas in the main workbook - they should have a $ sign in front
of both the column letter and row number for each cell referenced in the one
now closed. They should look something like this:
='C:\Documents and Settings\All
Users\Documents\[LooperData2.xls]LooperData'!$B$392
with the path to the file, the [filename.xls]! and the $c$r reference in the
other workbook.

Copy the formulas down for 69 rows (or across for 69 columns depending on
how you set things up)- so you now have something to work with for the other
69 files.

Now comes the hard part - the manual labor. Working with one set of data
for each of the other 69 workbooks, you can use Edit | Replace to change the
name of the workbook referenced in the formulas for that workbook.

Be sure you limit the changes made to the current selection or you'll keep
changing all the workbook names in all the cells each time, making no
progress at all. Since the path to the files is the same, and (if) the cells
were all located in the same places/sheets (and sheet names were the same)
then the only thing that needs to be changes is the workbook name, that part
within the square brackets, so you could then Edit | Replace
Find: [workbook1.xls]
Replace: [workbook2.xls]

Then repeat for each of the others, just changing the value in the Replace
parameter.

One thing about all of this - if you change the information in any of the 70
other workbooks, those changes will be reflected in this one also. If that
is undesirable, after you've finished making all of those changes, select all
of the cells with the formulas in them and first do Edit | Copy, then without
unselecting the group, use Edit | Paste Special and choose the [Values]
option. That will replace the formulas with the actual values just as if
you'd cut and pasted from each of the 70 worksheets - and changes in the
other workbooks will not affect the contents of the main one.

Sorry I couldn't offer you an easier solution. Given the 70 names of the
other workbooks, the sheet name(s) with the information on them and the
locations of the 15 pieces of data, a short macro could have been written to
do all that work in fairly short order.



"aggie mom in dallas" wrote:

I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks

Ron:

I have a similar problem and wanted to know if there was a way I could do
the following within normal Excel function or if I had to do some VBA code.
Please see below:

Like aggie mom, I have data to gather from many source worksheets. However,
each sheet has a date in it's file name that I would like to reference to the
date column in the work sheet that I want the data in.

This I can do:

='I:\daily\[steam_totalizers_24hrs_09182006_0600.xls]data'!$B$733-'I:\daily\[steam_totalizers_24hrs_09182006_0600.xls]data'!$B$732

I would like to do this, or something similar:

="'I:\daily\[steam_totalizers_24hrs_0"&month(a126)&day(a126)&ye ar(a126)&"_0600.xls]data'!$B$733"-"'I:\daily\[steam_totalizers_24hrs_0"&month(a126)&day(a126)&ye ar(a126)"_0600.xls]data'!$B$732"
where cell a126 displays 9/18/2006

Can I do this?

Thanks!!



"Ron de Bruin" wrote:

Try

http://www.rondebruin.nl/summary2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"aggie mom in dallas" wrote in message
...
I have 70 workbooks, each for different customer containing identical
worksheet - with customer specific data.
How can I get 15 pieces of data from each identical worksheet into one
worksheet in a list format?

I tried consolidate - but either I didn't do it right or it is just as
manual as copy/paste would be.

Thanks