Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
steve
 
Posts: n/a
Default From several workbooks onto one excel worksheet

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default From several workbooks onto one excel worksheet

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks

  #3   Report Post  
Posted to microsoft.public.excel.misc
steve
 
Posts: n/a
Default From several workbooks onto one excel worksheet

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks

  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default From several workbooks onto one excel worksheet

I don't recall there being an Excel limit on the number of consolidation
ranges.
If you're using DataConsolidate and the the workbooks and ranges are always
in flux (adding/deleting workbooks, changing data ranges, etc) be aware of
the maintenance issues. If the model becomes unwieldy, it's time to either
automate the maintenance or find another solution. Perhaps the Template
Wizard with Data Tracking.

***********
Regards,
Ron


"steve" wrote:

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks

  #5   Report Post  
Posted to microsoft.public.excel.misc
steve
 
Posts: n/a
Default From several workbooks onto one excel worksheet

Thanks Ron

The workbooks will be in the same format but the data will be changed each
month. This is all for fitness test results for about 30 branches. Each
branch will have to send me a copy of their fitness data each month so I can
keep an overall summary of results. It's just that each document has about 6
or 7 worksheets in it (age groups) and i didn't want to overload the system.

When it comes to the second month can I still use your suggested solution to
add to the first set of consolidated data? Refresh data replaced data from
the same sheet which is not really what I was after as i need to have one
consolidated sheet for each age group at the end of the year.


Many thanks
Rgds
Steve



"Ron Coderre" wrote:

I don't recall there being an Excel limit on the number of consolidation
ranges.
If you're using DataConsolidate and the the workbooks and ranges are always
in flux (adding/deleting workbooks, changing data ranges, etc) be aware of
the maintenance issues. If the model becomes unwieldy, it's time to either
automate the maintenance or find another solution. Perhaps the Template
Wizard with Data Tracking.

***********
Regards,
Ron


"steve" wrote:

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks



  #6   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default From several workbooks onto one excel worksheet

Since the new set of workbooks will not include the same data as the previous
set, Excel will want to overwrite the previous data. BUT...there is a simple
method to prevent that:

Copy the column headings to the range of cells just below the data range.
Example:
If your data range is in cells A1:E12, then copy A1:E1 to A13:E13.

InsertNameDefine
Names in Workbook: (Select the query name from the list)
Refers to: (Select the column headings you pasted).
Click the [OK] button
Right click on those new Col Headings and select Refresh Data.

Now, Excel will return the new data to that range, effectively appending it
to the previous data.

Does that help?

***********
Regards,
Ron


"steve" wrote:

Thanks Ron

The workbooks will be in the same format but the data will be changed each
month. This is all for fitness test results for about 30 branches. Each
branch will have to send me a copy of their fitness data each month so I can
keep an overall summary of results. It's just that each document has about 6
or 7 worksheets in it (age groups) and i didn't want to overload the system.

When it comes to the second month can I still use your suggested solution to
add to the first set of consolidated data? Refresh data replaced data from
the same sheet which is not really what I was after as i need to have one
consolidated sheet for each age group at the end of the year.


Many thanks
Rgds
Steve



"Ron Coderre" wrote:

I don't recall there being an Excel limit on the number of consolidation
ranges.
If you're using DataConsolidate and the the workbooks and ranges are always
in flux (adding/deleting workbooks, changing data ranges, etc) be aware of
the maintenance issues. If the model becomes unwieldy, it's time to either
automate the maintenance or find another solution. Perhaps the Template
Wizard with Data Tracking.

***********
Regards,
Ron


"steve" wrote:

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks

  #7   Report Post  
Posted to microsoft.public.excel.misc
steve
 
Posts: n/a
Default From several workbooks onto one excel worksheet

Thanks Ron. Sorry for the delay in replying. I will give this a try.
I wonder whether there might be a way of reducing the amount of work by
creating a summary worksheet in every workbook containing a copy of each of
the worksheets in that document. Then I would only have to MS query from one
sheet in each document to get to the master summary document. Is that
possible? or would i be trying to do too much linking on the same data?

Many thanks again for your help
Rgds
Steve

"Ron Coderre" wrote:

Since the new set of workbooks will not include the same data as the previous
set, Excel will want to overwrite the previous data. BUT...there is a simple
method to prevent that:

Copy the column headings to the range of cells just below the data range.
Example:
If your data range is in cells A1:E12, then copy A1:E1 to A13:E13.

InsertNameDefine
Names in Workbook: (Select the query name from the list)
Refers to: (Select the column headings you pasted).
Click the [OK] button
Right click on those new Col Headings and select Refresh Data.

Now, Excel will return the new data to that range, effectively appending it
to the previous data.

Does that help?

***********
Regards,
Ron


"steve" wrote:

Thanks Ron

The workbooks will be in the same format but the data will be changed each
month. This is all for fitness test results for about 30 branches. Each
branch will have to send me a copy of their fitness data each month so I can
keep an overall summary of results. It's just that each document has about 6
or 7 worksheets in it (age groups) and i didn't want to overload the system.

When it comes to the second month can I still use your suggested solution to
add to the first set of consolidated data? Refresh data replaced data from
the same sheet which is not really what I was after as i need to have one
consolidated sheet for each age group at the end of the year.


Many thanks
Rgds
Steve



"Ron Coderre" wrote:

I don't recall there being an Excel limit on the number of consolidation
ranges.
If you're using DataConsolidate and the the workbooks and ranges are always
in flux (adding/deleting workbooks, changing data ranges, etc) be aware of
the maintenance issues. If the model becomes unwieldy, it's time to either
automate the maintenance or find another solution. Perhaps the Template
Wizard with Data Tracking.

***********
Regards,
Ron


"steve" wrote:

Thanks Ron. I got that to work (after a fashion!) Many thanks for the
help. Do you know if there is a limit to the number of workbooks one can
consolidate into one ?

Rgds
Steve

"Ron Coderre" wrote:

If the data ranges have only one row of column headings and only one column
of row headings....check out DataConsolidation.

But...if there are multiple columns of row headings (and only one row of
column headings)...You might be able to use MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts:

In my example, ranges named rngNameAmt1 (the ranges contain 2 columns: Name
and Amount) are consolidated from 4 Excel workbooks into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),
---Col Headings in different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in Named Ranges.
---You may use the same range name in different wkbks.

(Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

(Note: you can create the above SQL codes in Excel by using formulas and
just paste the sQL range into MS Query's SQL window.)

Return the data to Excel.

Once that is done....all you need to do to get the latest data is click in
the data range then DataRefresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with?

Ron


"steve" wrote:

Hi
I'm using excel 2002. I need to import data that is held by several people
in their own excel workbooks and put their data onto one excel worksheet so I
have a summary of everyones data. We will all have the same columns and rows
etc.

I tried the simple copy/ paste and copy and move functions but there are
some formulae in the sheets that were lost during the move.

I have tried 'data import' route as suggested by others, but when i come to
import the second person's data the 'import data' is greyed out if I want to
put the data below the first set. If I go to put it alongside the first one
it doesn't grey out and I can import it there. However I want to put it below
the first data!

Anyone any ideas how I can put the second set of data below the first, and
obviously subsequent data below the second etc (and why it should grey out?)

Many thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 04:21 PM
exporting excel worksheet to word morrowkd Links and Linking in Excel 1 May 11th 05 11:23 PM
How do I show more workbooks in the Excel taskpane? Wideejay Excel Discussion (Misc queries) 1 April 28th 05 06:54 AM
Create New Microsoft Excel Worksheet Has 3 Sheets Bassam Setting up and Configuration of Excel 0 February 9th 05 09:51 PM


All times are GMT +1. The time now is 11:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"