TheDude0901
New Member
- Joined
- Jan 9, 2014
- Messages
- 10
Greetings,
I'm an old database guy and I think it's caused me to design my Excel app in a way that won't work very well. I am trying to design a spreadsheet to track paint usage and emissions for environmental reporting. Anyone dealing with a Title V permit will know exactly what I'm talking about. Anyway, I need to track the type of paint, quantity used, the specific chemicals in each paint and calculate the amounts of those chemicals based upon the amount of paint. So, I've started laying out tables, or worksheets in the Excel world, in a way to do this.
Since pictures are better than words, here's what my tables look like:
Paint Usage
This gets updated every month with our usage data
<tbody>
</tbody>
Paint Properties
This is a fixed table and is used for lookup.
<tbody>
</tbody>
Chemical Properties
This is a fixed table used for lookup
<tbody>
</tbody>
What I need is the following:
I need to get a breakdown of the chemical usage with the chemical numbers as rows and the month/date as the columns. It needs to be summarized by month and then a total across the dates.
<tbody>
</tbody>
To find these number it's
Paint Quantity * Density * Percent of Chemical
Getting paint quantities was easy with a pivot table. However, I'm not sure where to go next. I thought I may need to somehow create another pivot table with the paint information and then another pivot table with the paint and chemical pivot tables included.... I don't know....
This final chemical summary needs to be dynamic as the paint usage will vary through the year. Also, it's on a rolling year so when I add June 2015 data, June 2014 will fall off.
Now, if this was a SQL database I could write a query to get me what I need. However, this is Excel and I'm kinda out of my element.
Perhaps I'm laying out the tables all wrong? Any suggestions are more than welcome.
best regards,
The Dude
I'm an old database guy and I think it's caused me to design my Excel app in a way that won't work very well. I am trying to design a spreadsheet to track paint usage and emissions for environmental reporting. Anyone dealing with a Title V permit will know exactly what I'm talking about. Anyway, I need to track the type of paint, quantity used, the specific chemicals in each paint and calculate the amounts of those chemicals based upon the amount of paint. So, I've started laying out tables, or worksheets in the Excel world, in a way to do this.
Since pictures are better than words, here's what my tables look like:
Paint Usage
This gets updated every month with our usage data
| Date | Paint_Part_Number | Quantity |
| 4/1/2015 | xx-1234 | 10 |
| 4/1/2015 | yy-5678 | 20 |
| 5/1/2015 | zz-1592 | 30 |
| 5/1/2015 | xx-1234 | 15 |
| 6/1/2015 | yy-5678 | 10 |
<tbody>
</tbody>
Paint Properties
This is a fixed table and is used for lookup.
| Part Part Number | Paint Name | Type | Density (lb/gal) | 1111-11-1 | 2222-22-2 | 3333-33-3 | 4444-44-4 | 5555-55-5 | 6666-66-6 |
| xx-1234 | Blue | Liquid | 9.58 | 45% | 27% | 10% | |||
| yy-5678 | Black | Liquid | 12.89 | 15% | 34% | 19% | |||
| zz-1592 | Yellow | Liquid | 11.1 | 45% | 45% | ||||
<tbody>
</tbody>
Chemical Properties
This is a fixed table used for lookup
| Chemical Number | Chemical Name | Reportable | Reportable Quantity (lbs) |
| 1111-11-1 | Bad Stuff | Yes | 10000 |
| 2222-22-2 | Really Bad Stuff | Yes | 100 |
| 3333-33-3 | Not so bad stuff | No | |
| 4444-44-4 | Pigment | No | |
| 5555-55-5 | Really, really bad stuff | Yes | 10 |
<tbody>
</tbody>
What I need is the following:
I need to get a breakdown of the chemical usage with the chemical numbers as rows and the month/date as the columns. It needs to be summarized by month and then a total across the dates.
| Chemical Name | Chemical Number | 4/1/2015 (lbs) | 5/1/2015 (lbs) | 6/1/2015 (lbs) | Total (lbs) |
| Bad Stuff | 1111-11-1 | 15 | 2 | 12 | 29 |
| Really Bad Stuff | 2222-22-2 | 5 | 7 | 12 | |
| Not so bad stuff | 3333-33-3 | 10 | 20 | 30 | 60 |
| Total | 30 | 29 | 42 | 101 |
<tbody>
</tbody>
To find these number it's
Paint Quantity * Density * Percent of Chemical
Getting paint quantities was easy with a pivot table. However, I'm not sure where to go next. I thought I may need to somehow create another pivot table with the paint information and then another pivot table with the paint and chemical pivot tables included.... I don't know....
This final chemical summary needs to be dynamic as the paint usage will vary through the year. Also, it's on a rolling year so when I add June 2015 data, June 2014 will fall off.
Now, if this was a SQL database I could write a query to get me what I need. However, this is Excel and I'm kinda out of my element.
Perhaps I'm laying out the tables all wrong? Any suggestions are more than welcome.
best regards,
The Dude
