(¯`·._.·[ Extract Data From Multiple Sheets ]·._.·´¯)

Extract Data From Multiple Sheets





http://www.sbtankserve.com/SAMPLE.zip

This is a SAMPLE excel file that I use to enter weekly data at work

Data for 5 days is entered for five days on five sheets

DAY 1
DAY 2
DAY 3
DAY 4
DAY 5

The first 7 rows are used for day specific calculations and column
headers; Data is entered from the 7th row and below

The first column is used for day specific serial numbers

Maximum data range on any of the five Day pages is A1, L38


I want a simple formula/macro that will perform the following
functions

--------------------------------------------------------------------------------------------------
1) Copy all the data on the five sheets to a new sheet to a new sheet
called "Consolidated"

But the data must be only
- Values,
- Fonts and
- Background cell colors only!
--------------------------------------------------------------------------------------------------


2) Create a new sheet called "Dispatches"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the DPS column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the dispatches for the week

--------------------------------------------------------------------------------------------------

3) Create a new sheet called "Call Backs Made"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the "Phone Number"
column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week


--------------------------------------------------------------------------------------------------

4) Create a new sheet called "One Call's"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the "1 Call"
column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week


--------------------------------------------------------------------------------------------------




- Hyperdreamz

Have you considered using a database for this? That would make your reporting
and summarizing MUCH easier.

On Wed, 29 Sep 2004 08:41:24 -0500, hyperdreamz
wrote:


Extract Data From Multiple Sheets





http://www.sbtankserve.com/SAMPLE.zip

This is a SAMPLE excel file that I use to enter weekly data at work

Data for 5 days is entered for five days on five sheets

DAY 1
DAY 2
DAY 3
DAY 4
DAY 5

The first 7 rows are used for day specific calculations and column
headers; Data is entered from the 7th row and below

The first column is used for day specific serial numbers

Maximum data range on any of the five Day pages is A1, L38


I want a simple formula/macro that will perform the following
functions

--------------------------------------------------------------------------------------------------
1) Copy all the data on the five sheets to a new sheet to a new sheet
called "Consolidated"

But the data must be only
- Values,
- Fonts and
- Background cell colors only!
--------------------------------------------------------------------------------------------------


2) Create a new sheet called "Dispatches"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the DPS column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the dispatches for the week

--------------------------------------------------------------------------------------------------

3) Create a new sheet called "Call Backs Made"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the "Phone Number"
column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week


--------------------------------------------------------------------------------------------------

4) Create a new sheet called "One Call's"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1,
Day 2, Day 3, Day 4, and Day 5) that have entries in the "1 Call"
column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date
Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week


--------------------------------------------------------------------------------------------------




- Hyperdreamz