Remember Me?

#1
May 9th 05, 07:41 AM
 sansk_23 Posts: n/a
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
May 9th 05, 09:17 AM
 Max Posts: n/a

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
May 9th 05, 12:52 PM
 Pank Mehta Posts: n/a

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
May 10th 05, 02:00 AM
 Max Posts: n/a

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
----

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Ian Charts and Charting in Excel 13 March 24th 05 07:46 PM Grafur Excel Worksheet Functions 2 February 19th 05 03:08 AM [email protected] Excel Discussion (Misc queries) 1 February 15th 05 02:45 AM seve Excel Discussion (Misc queries) 2 January 15th 05 09:22 PM Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM

All times are GMT +1. The time now is 09:40 PM.