Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 9th 05, 07:41 AM
sansk_23
 
Posts: n/a
Default Append the data given in diff sheets of an Excel File to one sheet

if i have some & similar data (same format - eg. date wise sales sheets in a
file) in different worksheets of an EXCEL workbook (eg. A1..Z100 in Sheet1
for Ist day of the month, A1..Z100 in Sheet2 for the 2nd day of the month,
A1..Z100 in Sheet3 for the 3rd day of the month, ......& so on - soforth for
a complete month (Sales data except of Saturdays & Sundays).....

How do i copy the Sales data from all the Sheets of the workbook to the
Sheet32 ?
The sales data shd be copied in the following format (preferaably automated
- PLS. DO NOT SUGGEST : Select / copy & paste).

Sheet32
A1..Z100 - Sales data from Sheet1. (First day sales)
A101..Z200 - Sales data from Sheet2. (2nd day sales)
A201..Z300 - Sales data from Sheet3. (3rd day sales)
..
..
..
The data from each sheet should be copied to Sheet32 at the end of the Sales
data copied from the previous sheet in vertical manner.
The objective is to consolidate a months data.
(This is like concatenation, but of an array - similar to the adding of the
records in a database thru an ERP / GUI.)

PLS. SUGGEST / HELP.


  #2   Report Post  
Old May 9th 05, 09:17 AM
Max
 
Posts: n/a
Default

One way ..

Assuming 31 source sheets named as: Sheet1, Sheet2, ... Sheet31, with data
in A1:Z100 in each sheet to be extracted over and placed sequentially one
below the other

In Sheet32
-------------
Put in A1:

=OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1:A1)-1)/100)+1&"!$A$1"),MOD(ROWS($A$1
:A1)-1,100),COLUMNS($A$1:A1)-1)

Copy A1 across to Z1, fill down to Z3100
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----
"sansk_23" wrote in message
...
if i have some & similar data (same format - eg. date wise sales sheets in

a
file) in different worksheets of an EXCEL workbook (eg. A1..Z100 in Sheet1
for Ist day of the month, A1..Z100 in Sheet2 for the 2nd day of the month,
A1..Z100 in Sheet3 for the 3rd day of the month, ......& so on - soforth

for
a complete month (Sales data except of Saturdays & Sundays).....

How do i copy the Sales data from all the Sheets of the workbook to the
Sheet32 ?
The sales data shd be copied in the following format (preferaably

automated
- PLS. DO NOT SUGGEST : Select / copy & paste).

Sheet32
A1..Z100 - Sales data from Sheet1. (First day sales)
A101..Z200 - Sales data from Sheet2. (2nd day sales)
A201..Z300 - Sales data from Sheet3. (3rd day sales)
.
.
.
The data from each sheet should be copied to Sheet32 at the end of the

Sales
data copied from the previous sheet in vertical manner.
The objective is to consolidate a months data.
(This is like concatenation, but of an array - similar to the adding of

the
records in a database thru an ERP / GUI.)

PLS. SUGGEST / HELP.



  #3   Report Post  
Old May 9th 05, 12:52 PM
Pank Mehta
 
Posts: n/a
Default

Look at http://www.rondebruin.nl/copy2.htm I found it very useful.

"Max" wrote:

One way ..

Assuming 31 source sheets named as: Sheet1, Sheet2, ... Sheet31, with data
in A1:Z100 in each sheet to be extracted over and placed sequentially one
below the other

In Sheet32
-------------
Put in A1:

=OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1:A1)-1)/100)+1&"!$A$1"),MOD(ROWS($A$1
:A1)-1,100),COLUMNS($A$1:A1)-1)

Copy A1 across to Z1, fill down to Z3100
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sansk_23" wrote in message
...
if i have some & similar data (same format - eg. date wise sales sheets in

a
file) in different worksheets of an EXCEL workbook (eg. A1..Z100 in Sheet1
for Ist day of the month, A1..Z100 in Sheet2 for the 2nd day of the month,
A1..Z100 in Sheet3 for the 3rd day of the month, ......& so on - soforth

for
a complete month (Sales data except of Saturdays & Sundays).....

How do i copy the Sales data from all the Sheets of the workbook to the
Sheet32 ?
The sales data shd be copied in the following format (preferaably

automated
- PLS. DO NOT SUGGEST : Select / copy & paste).

Sheet32
A1..Z100 - Sales data from Sheet1. (First day sales)
A101..Z200 - Sales data from Sheet2. (2nd day sales)
A201..Z300 - Sales data from Sheet3. (3rd day sales)
.
.
.
The data from each sheet should be copied to Sheet32 at the end of the

Sales
data copied from the previous sheet in vertical manner.
The objective is to consolidate a months data.
(This is like concatenation, but of an array - similar to the adding of

the
records in a database thru an ERP / GUI.)

PLS. SUGGEST / HELP.




  #4   Report Post  
Old May 10th 05, 02:00 AM
Max
 
Posts: n/a
Default

And I'll betcha you can complete the suggested formula approach and get it
up and running even before Ron's page loads completely <bg ! (Just
kidding). One is seldom bereft of choices in these excel newsgroups.
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----




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
Overlay charts Ian Charts and Charting in Excel 13 March 24th 05 07:46 PM
How do I transfer data between Excell sheets? Grafur Excel Worksheet Functions 2 February 19th 05 03:08 AM
How do I create a list in excel that contains external data? [email protected] Excel Discussion (Misc queries) 1 February 15th 05 02:45 AM
populating sheets based on data from parent sheets seve Excel Discussion (Misc queries) 2 January 15th 05 09:22 PM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017