I think I've designed myself into a corner

[TheDude0901]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Paint_Part_Number[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]4/1/2015[/TD]
[TD]xx-1234[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4/1/2015[/TD]
[TD]yy-5678[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5/1/2015[/TD]
[TD]zz-1592[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5/1/2015[/TD]
[TD]xx-1234[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]yy-5678[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Paint Properties
This is a fixed table and is used for lookup.
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Part Part Number[/TD]
[TD]Paint Name[/TD]
[TD]Type[/TD]
[TD]Density (lb/gal)[/TD]
[TD]1111-11-1[/TD]
[TD]2222-22-2[/TD]
[TD]3333-33-3[/TD]
[TD]4444-44-4[/TD]
[TD]5555-55-5[/TD]
[TD]6666-66-6[/TD]
[/TR]
[TR]
[TD]xx-1234[/TD]
[TD]Blue[/TD]
[TD]Liquid[/TD]
[TD]9.58[/TD]
[TD]45%[/TD]
[TD][/TD]
[TD]27%[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yy-5678[/TD]
[TD]Black[/TD]
[TD]Liquid[/TD]
[TD]12.89[/TD]
[TD][/TD]
[TD]15%[/TD]
[TD]34%[/TD]
[TD]19%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zz-1592[/TD]
[TD]Yellow[/TD]
[TD]Liquid[/TD]
[TD]11.1[/TD]
[TD]45%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Chemical Properties
This is a fixed table used for lookup
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Chemical Number[/TD]
[TD]Chemical Name[/TD]
[TD]Reportable[/TD]
[TD]Reportable Quantity (lbs)[/TD]
[/TR]
[TR]
[TD]1111-11-1[/TD]
[TD]Bad Stuff[/TD]
[TD]Yes[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]2222-22-2[/TD]
[TD]Really Bad Stuff[/TD]
[TD]Yes[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3333-33-3[/TD]
[TD]Not so bad stuff[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4444-44-4[/TD]
[TD]Pigment[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5555-55-5[/TD]
[TD]Really, really bad stuff[/TD]
[TD]Yes[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Chemical Name[/TD]
[TD]Chemical Number[/TD]
[TD]4/1/2015 (lbs)[/TD]
[TD]5/1/2015 (lbs)[/TD]
[TD]6/1/2015 (lbs)[/TD]
[TD]Total (lbs)[/TD]
[/TR]
[TR]
[TD]Bad Stuff[/TD]
[TD]1111-11-1[/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]Really Bad Stuff[/TD]
[TD]2222-22-2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Not so bad stuff[/TD]
[TD]3333-33-3[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]29[/TD]
[TD]42[/TD]
[TD]101[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[SpillerBD]

No you haven't painted yourself into a corner.
Your primary reporting tool (in my opinion) is PivotTables. You could accomplish your chemical component calculation with related data tables in Pivot Tables, Helper columns or calculated fields for Pivot Tables. (Your background with SQL may give you an advantage in learning the DAX.)
Helper columns can be easier to implement in some ways but adds to the clutter and formula load on the sheet.

The calculated field option is my main suggestion and your existing tables could easily be used for named constants giving you your breakdowns. I'm getting into my month-end so excuse me if I don't ad much else.

 

[TheDude0901]

Greetings,

I've tried several variations of pivot tables using the chemical properties but I'm getting nowhere. I believe I need to use calculated fields but I don't know how to add them in the pivot table. It always wants an existing row/column. I just can't create a new one on the fly.

I can't make the mental leap to join the paint usage pivot table with the paint properties and chemical properties to come up what I need. I __feel__ like it __should__ be done using a pivot table but I don't know where to go from there.

I'm completely stuck at this point. Can anyone help???

Best regards,
The Dude

 

[SpillerBD]

Can you PM a link to a sample copy of your data?
PS.What version of Excel are your using? 2007,2010 2013 or other?