Updating a worksheet within a workbook

[gray78]

I have a workbook which has several worksheets (linked) within it. One worksheet is called "download 2005" which feeds many of the other worksheets. "Download 2005" has totals by month which updates other work sheets. I also have a worksheet named "Current mth by facility". I would like to be able to update this "Current mth by facility" worksheet with the new month automatically. Is this possible?

Thanks

 

[SkipVought]

gray,

It is almost never a good idea, IMHO, to do aggregation in source data.

How is your source data structured?

How do you aggregate your source data?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[SkipVought]

gray,

It is almost never a good idea, IMHO, to do aggregation in source data.

How is your source data structured?

How do you aggregate your source data for any month?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

Skip

My source data is downloaded from a database and through a macro it is copied and pasted into a worksheet "Download 2005". It is broken out by month (ie. "8-Aug"). The download is done monthly and as sometimes previous months data can change, the entire table is downloaded (Jan.-current month) each time and overwrites the existing data.

Gray78

 

[SkipVought]

I'd want to be working with the resultset from the query and NOT the macro manupulated data.

You still did not answer my 2 questions. I need to know how the data is structured in the source sheet and in the month sheet.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

They are both number(general) no decimals.

Hope this helps

 

[SkipVought]

Do you have something against posting a sample of data in each?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

Not at all, how would I post the two worksheets

 

[SkipVought]

Exa...
[tt]
CC MG MachNum Nomen
5AD 17182 1718203 CNC MILL K&T 3-AXIS (VB-4) 40" TRAVEL
5B1 11443 1144301 CNC JIG BORER SIP MODEL 740 48" X 63" TABLE
5B1 11445 1144504 CNC JIG BORER SIP MODEL 740 WITH ROTARY TABLE

[/tt]



Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

download 2005:
Fac Id 1 7 8 7 4 6 2 3 9 Total Collisions Totals W/Injury Totals W/Injury Rma Totals Total Injuries Totals
Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries Total Collisions W/Injury W/Injury Rma Total Injuries
Qtr Month
1 1-Jan 8 0 0 0 2 0 0 0 1 0 0 0 1 0 0 0 14 0 0 0 7 3 0 3 23 1 0 1 36 12 2 14 92 16 2 18
2-Feb 6 1 2 3 15 2 0 2 12 0 0 0 12 0 1 1 24 4 3 7 38 8 5 13 107 15 11 26
3-Mar 12 0 1 1 8 1 0 1 1 0 0 0 7 0 1 1 14 2 1 3 24 1 2 3 46 6 12 18 112 10 17 27
Qtr Total 26 1 3 4 25 3 0 3 1 0 0 0 2 0 0 0 33 0 1 1 33 5 2 7 71 6 5 11 120 26 19 45 311 41 30 71
2 4-Apr 23 5 0 5 8 1 0 1 1 0 1 1 2 0 0 0 2 1 0 1 9 1 3 4 23 4 1 5 34 2 1 3 49 9 6 15 151 23 12 35
5-May 21 1 0 1 2 0 0 0 1 0 1 1 1 0 0 0 15 1 0 1 18 2 0 2 40 5 3 8 45 10 12 22 143 19 16 35
6-Jun 24 3 1 4 9 0 3 3 1 0 0 0 1 0 0 0 1 0 0 0 13 1 0 1 21 0 0 0 56 6 9 15 63 10 8 18 189 20 21 41
Qtr Total 68 9 1 10 19 1 3 4 3 0 2 2 3 0 0 0 4 1 0 1 37 3 3 6 62 6 1 7 130 13 13 26 157 29 26 55 483 62 49 111
3 7-Jul 13 1 3 4 8 3 1 4 6 1 0 1 26 1 0 1 52 14 7 21 57 10 6 16 162 30 17 47
8-Aug 18 2 2 4 7 1 1 2 2 1 0 1 15 0 1 1 31 0 0 0 38 4 5 9 58 10 7 17 169 18 16 34
9-Sep 11 3 2 5 6 1 1 2 3 1 0 1 10 0 0 0 11 2 0 2 32 3 0 3 49 8 9 17 122 18 12 30
Qtr Total 42 6 7 13 21 5 3 8 2 1 0 1 3 1 0 1 31 1 1 2 68 3 0 3 122 21 12 33 164 28 22 50 453 66 45 111
4 10-Oct 19 2 1 3 6 1 1 2 3 1 0 1 15 1 0 1 22 2 0 2 30 2 4 6 31 16 0 16 126 25 6 31
11-Nov 3 0 0 0 3 0 0 0
Qtr Total 19 2 1 3 6 1 1 2 3 1 0 1 15 1 0 1 22 2 0 2 33 2 4 6 31 16 0 16 129 25 6 31
current mo:
Facility Collision Rate: Collisions per Million Vehicles
Facility Total 1 2 3 4 5 6 7 8 9
Current Month 6.23 5.00 0.00 6.83 0.00 2.92 6.01 4.70 7.80 8.94
Last 12 Months Avg. 169.00 4.26 0.53 6.88 1.29 0.97 4.75 4.53 5.60 8.02
Year-to-Date Avg. 141.17 4.79 0.87 6.82 0.76 1.21 6.75 6.48 7.62 10.23
Year-to-Date Goal** 5.49 3.54 1.63 5.85 1.92 0.53 4.37 4.16 4.55 6.60
% Variance from YTD Goal (2,469.0%) (35.2%) 46.7% (16.5%) 60.5% (127.6%) (54.6%) (55.7%) (67.4%) (54.9%)

Facility Collision Rate: with Injuries per Million Vehicles
Facility Total 1 2 3 4 5 6 7 8 9
Current Month 1.22 1.11 0.00 1.62 0.00 0.00 0.40 1.34 0.00 2.62
Last 12 Months Avg. 33.00 0.66 0.08 1.39 0.26 0.28 0.59 0.84 0.65 2.48
Year-to-Date Avg. 30.50 0.95 0.00 1.92 0.19 0.40 0.57 1.60 0.77 3.70
Year-to-Date Goal** 1.19 0.53 0.35 1.20 0.37 0.17 0.69 0.98 0.88 2.54
% Variance from YTD Goal (2,469.0%) (80.0%) 100.0% (59.5%) 48.7% (136.4%) 17.5% (63.2%) 12.5% (46.0%)

Facility Collision Rate: with Serious Injuries per Million Vehicles
Facility Total 1 2 3 4 5 6 7 8 9
Current Month 30.00 0.56 0.00 0.72 0.00 1.46 0.00 0.67 0.00 1.54
Last 12 Months Avg. 18.00 0.39 0.08 0.80 0.26 0.00 0.21 0.56 0.50 1.41
Year-to-Date Avg. 17.75 0.56 0.19 1.03 0.19 0.20 0.31 0.93 0.66 2.34
Year-to-Date Goal** 0.69 0.30 0.23 0.86 0.15 0.17 0.20 0.51 0.71 1.38
% Variance from YTD Goal (2,469.0%) (86.1%) 17.5% (20.0%) (28.1%) (18.2%) (55.8%) (80.3%) 6.7% (69.2%)

 

[SkipVought]

Your data is structured as a REPORT (which is BAD NEWS!)

It is a difficult data analysis job to scrape a report. YOUR report is very complex, which would make it EXTERMELY DIFFICULT.

If your data were in NORMALIZED TABLE FORMAT, the reporting job could be done in SECONDS, for instance, using the Pivot table Wizard.

So my suggestion is, put your effort into getting the SOURCE DATA that someone used to generate this report.



Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

it is in table format. what i did was copy and past from an excel spreadsheet

 

[SkipVought]

What you pasted is not a table. It's a report.

Show me the table!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[SkipVought]

FYI...

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[gray78]

i'm stumped

 

[SkipVought]

No Table?

Hmmmmmmmmmmmmmm?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}