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

[hyperdreamz]

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 colum
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 followin
functions

--------------------------------------------------------------------------------------------------
1) Copy all the data on the five sheets to a new sheet to a new shee
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 Dat
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 Dat
Value" from each respective page

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


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

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 Dat
Value" from each respective page

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


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




- Hyperdreamz

 

[Myrna Larson]

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