Get pivot table data showing total of previously billed on statements

[Kimberlieh]

Hello MrExcel members,

I have a billing statement I'm working on, which is populated mainly from a pivot table. The columns I am struggling with are labeled "Previously Billed", "Total Earned", and "Quantity To-Date".

The pivot table includes all twelve months as headers, and the billing items are the rows. After a year, some months potentially could be blank.

If we want to reprint March's statement, for example, even if they're blank, we should only have the amounts for January and February in the "Previously Billed" and "Quantity To-Date columns, with March's billing in the column labeled "This Estimate". The "Total Billed" column should be a combination of just the three months, not the entire year, as it currently does.

Please, if anyone can offer assistance. I am open to VBA or formulas, whichever is recommended as the most efficient and easiest to maintain.

Thank you for your consideration helping me with this project.

Kimberlie

 

[Jerry Sullivan]

Hi Kimberlie,

If you post a small example of showing your PivotTable layout, and desired result in the billing statement, it will make it easier for people to offer suggestions.

 

[Kimberlieh]

Sure...

Pivot Table layout... (called Pivot Data)

(A4)Bid Item Number (B4)Description (C4)Prior Yr Qty (D4)Jan (E4)Feb (F4)Mar (G4)Apr (H4)May (I4)Jun (J4)July (K4)Aug (L4)Sep...
(Row 5) 10 Mobilization 1 1 1
(Row 6) 30 Temp Signs In Place 513.5 65 200
(Row 7) 470 Unit Pavers 650 35

_____________________________________________________________________________

Estimate Worksheet layout...(called Estimate)

User enters month into cell M8

(A12)Item # (B&C12 merged)Description (D12)Quantity To-Date (E12)Unit Price (F&G12)Total Earned (H&I12)Previously Earned (J12)This Estimate...
(Row 13) 10 Mobilization
(Row 14) 30 Temp Signs in Place
(Row 15) 470 Unit Pavers

_______________________________________________________________________________

If this is a July statement, the Quantity to-date should be;
Mobilization (2)
Temp Signs in Place (578.5)
Unit Pavers (685)

" " Total Earned (for conversation purposes, we'll say each item is $10 each)
Mobilization (20.00)
Temp Signs in Place (5,785.0)
Unit Pavers (6,850.)

" " previously earned would be the same as total earned.

" " This estimate each would be "0"

________________________________________________________________________________

If this was a September Statement, the Quantity to-date should be;
Mobilization (3)
Temp Signs in Place (778.5)
Unit Pavers (685)

" " Total Earned;
Mobilization (30.00)
Temp Signs in Place (7,785.0)
Unit Pavers (6,850.)

" " Previously Earned
Mobilization (20.00)
Temp Signs in Place (5,785.0)
Unit Pavers (6,850.)

" " This estimate
Mobilization (1)
Temp Signs in Place (200)
Unit Pavers (0)

Incidentally, I take the quantities and multiply them by the billing rate.

Does that help, or would the spreadsheet be a better explanation?

Kimberlie

 

[Jerry Sullivan]

The best option is to use one of the methods described in this link to post a range of cells. That way that others can paste the example data into a workbook.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Kimberlieh]

Data Base (Worksheet)


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 8.0px Arial; color: #4349aa }table.t1 {border-collapse: collapse}td.td1 {background-color: #000000 ; border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #000000 ; border-style: solid; border-width: 0.8px 1.0px 1.0px 1.0px; border-color: #38d4d6 #cbcbcb #cbcbcb #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Job Number[/TD]
[TD="class: td1"]Project Name[/TD]
[TD="class: td1"]Bid Item Number[/TD]
[TD="class: td1"]Bid Item Desccription[/TD]
[TD="class: td1"]Unit[/TD]
[TD="class: td1"]Bid Qty[/TD]
[TD="class: td1"]Bid Price[/TD]
[TD="class: td2"]Blank[/TD]
[TD="class: td1"]Prior Quantities[/TD]
[TD="class: td1"]Jan Qty[/TD]
[TD="class: td1"]Feb Qty[/TD]
[TD="class: td1"]Mar Qty[/TD]
[TD="class: td1"]Apr Qty[/TD]
[TD="class: td1"]May Qty[/TD]
[TD="class: td1"]Jun Qty[/TD]
[TD="class: td1"]July Qty[/TD]
[TD="class: td1"]Aug Qty[/TD]
[TD="class: td1"]Sep Qty[/TD]
[TD="class: td1"]Oct Qty[/TD]
[TD="class: td1"]Nov Qty[/TD]
[TD="class: td1"]Dec Qty[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 9.0px Arial; color: #3da779}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 9.0px Arial; color: #3da779}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 9.0px Arial; min-height: 10.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 9.0px Arial}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 9.0px Arial}table.t1 {border-collapse: collapse}td.td1 {background-color: #ffd5a9 ; border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #000000 ; border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {background-color: #000000 ; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #a7a7a7 #cbcbcb #a7a7a7 #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]10[/TD]
[TD="class: td1"]Mobilization[/TD]
[TD="class: td2"]LS[/TD]
[TD="class: td2"] 4.00 [/TD]
[TD="class: td2"]500.00[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"]1.00[/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]30[/TD]
[TD="class: td1"]Temporary Signs In Place[/TD]
[TD="class: td2"]SQFT[/TD]
[TD="class: td2"] 545.00 [/TD]
[TD="class: td2"]14.00[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"]513.50[/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]40[/TD]
[TD="class: td1"]Temporary Barricades, Type II[/TD]
[TD="class: td2"]EA[/TD]
[TD="class: td2"] 24.00 [/TD]
[TD="class: td2"]45.00[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"]39.00[/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]50[/TD]
[TD="class: td1"]Temporary Barricades, Type III[/TD]
[TD="class: td2"]EA[/TD]
[TD="class: td2"] 5.00 [/TD]
[TD="class: td2"]95.00[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"]5.00[/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]80[/TD]
[TD="class: td1"]Temporary Type 1 Fence[/TD]
[TD="class: td2"]Foot[/TD]
[TD="class: td2"] 153.00 [/TD]
[TD="class: td2"]10.50[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"]160.00[/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
[TR]
[TD="class: td1"]15031[/TD]
[TD="class: td2"]Fossil Heritage Trail (Fossil)[/TD]
[TD="class: td2"]470[/TD]
[TD="class: td1"]Unit Pavers[/TD]
[TD="class: td2"]SF[/TD]
[TD="class: td2"] 1,600.00 [/TD]
[TD="class: td2"]12.50[/TD]
[TD="class: td3"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[TD="class: td2"][/TD]
[/TR]
</tbody>[/TABLE]


Pivot Data (Pivot table worksheet)

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial; color: #4349aa }p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial; color: #4349aa ; min-height: 11.0px}p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial; color: #4349aa }table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.5px 1.0px 1.5px 1.5px; border-color: #4349aa #cbcbcb #4349aa #4349aa ; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {border-style: solid; border-width: 1.5px 0.8px 1.5px 1.0px; border-color: #4349aa #4349aa #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {border-style: solid; border-width: 1.5px 1.0px 1.5px 1.0px; border-color: #4349aa #cbcbcb #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {border-style: solid; border-width: 1.5px 1.5px 1.5px 1.0px; border-color: #4349aa #4349aa #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {background-color: #d6d7ff ; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #d6d7ff #cbcbcb #ffffff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #d6d7ff #4349aa #d6d7ff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #d6d7ff #cbcbcb #d6d7ff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Bid Item Number[/TD]
[TD="class: td2"]Description[/TD]
[TD="class: td3"] Prior Quantities[/TD]
[TD="class: td3"]Jan[/TD]
[TD="class: td3"]Feb[/TD]
[TD="class: td3"]Mar[/TD]
[TD="class: td3"]Apr[/TD]
[TD="class: td3"]May[/TD]
[TD="class: td3"]Jun[/TD]
[TD="class: td3"]July[/TD]
[TD="class: td3"]Aug[/TD]
[TD="class: td3"]Sep[/TD]
[TD="class: td3"]Oct[/TD]
[TD="class: td3"]Nov[/TD]
[TD="class: td4"]Dec[/TD]
[/TR]
[TR]
[TD="class: td5"]10[/TD]
[TD="class: td6"]Mobilization[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"]1[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]30[/TD]
[TD="class: td6"]Temporary Signs In Place[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"]513.5[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]40[/TD]
[TD="class: td6"]Temporary Barricades, Type II[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"]39[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]50[/TD]
[TD="class: td6"]Temporary Barricades, Type III[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"]5[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]80[/TD]
[TD="class: td6"]Temporary Type 1 Fence[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"]160[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]470[/TD]
[TD="class: td6"]Unit Pavers[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]560[/TD]
[TD="class: td6"]Remove Existing Signs[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
[TR]
[TD="class: td5"]570[/TD]
[TD="class: td6"]Remove & Reinstall Existing Signs[/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[TD="class: td7"][/TD]
[/TR]
</tbody>[/TABLE]

Estimate (Worksheet) Most information comes from the Pivot Table

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px 'Times New Roman'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 11.0px Arial; min-height: 12.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 12.0px Helvetica; min-height: 14.0px}p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 12.0px Helvetica}p.p7 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Arial; min-height: 14.0px}p.p8 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 11.0px Arial}p.p9 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Arial; min-height: 12.0px}p.p10 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 12.0px 'Times New Roman'; min-height: 15.0px}p.p11 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p12 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 12.0px 'Times New Roman'; min-height: 15.0px}p.p13 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px 'Times New Roman'}p.p14 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p15 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px 'Times New Roman'}p.p16 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial}p.p17 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 9.0px Arial}p.p18 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p19 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial; min-height: 11.0px}p.p20 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {border-style: solid; border-width: 1.0px 1.0px 0.8px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td8 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td9 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #000000 #000000 #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td10 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #000000 #cbcbcb #cbcbcb #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td11 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #000000 #000000 #cbcbcb #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td12 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td13 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #cbcbcb #000000 #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td14 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #cbcbcb #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td15 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #cbcbcb #cbcbcb #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td16 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #000000 #000000 #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td17 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td18 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #000000 #cbcbcb #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}</style><style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px 'Times New Roman'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 11.0px Arial; min-height: 12.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 12.0px Helvetica; min-height: 14.0px}p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 12.0px Helvetica}p.p7 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Arial; min-height: 14.0px}p.p8 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 11.0px Arial}p.p9 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Arial; min-height: 12.0px}p.p10 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 12.0px 'Times New Roman'; min-height: 15.0px}p.p11 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p12 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 12.0px 'Times New Roman'; min-height: 15.0px}p.p13 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px 'Times New Roman'}p.p14 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px 'Times New Roman'; min-height: 11.0px}p.p15 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px 'Times New Roman'}p.p16 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial}p.p17 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 9.0px Arial}p.p18 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p19 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial; min-height: 11.0px}p.p20 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {background-color: #000000; border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {border-style: solid; border-width: 1.0px 1.0px 0.8px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td8 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td9 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #000000 #000000 #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td10 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #000000 #cbcbcb #cbcbcb #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td11 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #000000 #000000 #cbcbcb #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td12 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #000000 #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td13 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #cbcbcb #000000 #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td14 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #cbcbcb #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td15 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #cbcbcb #cbcbcb #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td16 {border-style: solid; border-width: 1.0px 0.8px 1.0px 1.0px; border-color: #000000 #000000 #000000 #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td17 {border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}td.td18 {border-style: solid; border-width: 1.0px 1.0px 1.0px 0.8px; border-color: #000000 #cbcbcb #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1, colspan: 2"]Subcontractor:
[/TD]
[TD="class: td2, colspan: 3"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]Month
[/TD]
[/TR]
[TR]
[TD="class: td1, colspan: 2"]Address:
[/TD]
[TD="class: td3"]

[/TD]
[TD="class: td3"]

[/TD]
[TD="class: td4"]

[/TD]
[TD="class: td1, colspan: 2"]Contract No.
[/TD]
[TD="class: td5"]XXXXX
[/TD]
[TD="class: td5"]

[/TD]
[TD="class: td5"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td6"]Mar
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td3, colspan: 2"]

[/TD]
[TD="class: td4"]

[/TD]
[TD="class: td1, colspan: 2"]Project Name:
[/TD]
[TD="class: td5, colspan: 4"]Fossil Heritage Trail (Fossil)
[/TD]
[TD="class: td5"]

[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td8"]

[/TD]
[TD="class: td8"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td7"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td9"]

[/TD]
[TD="class: td10"]

[/TD]
[TD="class: td9"]

[/TD]
[TD="class: td11"]Quantity
[/TD]
[TD="class: td11"]Unit
[/TD]
[TD="class: td10"]Total
[/TD]
[TD="class: td9"]

[/TD]
[TD="class: td11, colspan: 2"]Previously
[/TD]
[TD="class: td12"]THIS
[/TD]
[TD="class: td12"]Original
[/TD]
[TD="class: td12"]Contract
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td13"]Item #
[/TD]
[TD="class: td14, colspan: 2"]Description
[/TD]
[TD="class: td14"]To-Date
[/TD]
[TD="class: td14"]Price
[/TD]
[TD="class: td15"]Earned
[/TD]
[TD="class: td13"]

[/TD]
[TD="class: td15"]Earned
[/TD]
[TD="class: td13"]

[/TD]
[TD="class: td7"]ESTIMATE
[/TD]
[TD="class: td7"]Quantity
[/TD]
[TD="class: td7"]Amount
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td16"]10
[/TD]
[TD="class: td17, colspan: 2"]Mobilization
[/TD]
[TD="class: td17"] 1.00
[/TD]
[TD="class: td17"] 500.00
[/TD]
[TD="class: td18"]500.00
[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td18"]

[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td8"] 500.00
[/TD]
[TD="class: td8"]4.00
[/TD]
[TD="class: td8"]2,000.00
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td16"]30
[/TD]
[TD="class: td17, colspan: 2"]Temporary Signs In Place
[/TD]
[TD="class: td17"] 513.50
[/TD]
[TD="class: td17"] 14.00
[/TD]
[TD="class: td18"]7,189.00
[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td18"]

[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td8"] 7,189.00
[/TD]
[TD="class: td8"]545.00
[/TD]
[TD="class: td8"]7,630.00
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td16"]40
[/TD]
[TD="class: td17, colspan: 2"]Temporary Barricades, Type II
[/TD]
[TD="class: td17"] 39.00
[/TD]
[TD="class: td17"] 45.00
[/TD]
[TD="class: td18"]1,755.00
[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td18"]

[/TD]
[TD="class: td16"]

[/TD]
[TD="class: td8"] 1,755.00
[/TD]
[TD="class: td8"]24.00
[/TD]
[TD="class: td8"]1,080.00
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
</tbody>[/TABLE]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial; color: #4349aa }p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial; color: #4349aa ; min-height: 11.0px}p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 10.0px Arial; color: #4349aa }table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.5px 1.0px 1.5px 1.5px; border-color: #4349aa #cbcbcb #4349aa #4349aa ; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {border-style: solid; border-width: 1.5px 0.8px 1.5px 1.0px; border-color: #4349aa #4349aa #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {border-style: solid; border-width: 1.5px 1.0px 1.5px 1.0px; border-color: #4349aa #cbcbcb #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {border-style: solid; border-width: 1.5px 1.5px 1.5px 1.0px; border-color: #4349aa #4349aa #4349aa #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {background-color: #d6d7ff ; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #d6d7ff #cbcbcb #ffffff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #d6d7ff #4349aa #d6d7ff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #d6d7ff #cbcbcb #d6d7ff #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}</style>

 

[Jerry Sullivan]

Kimberlie,

Thanks for posting the screenshots.

In the screenshot for Estimate, the values shown for Quantity to Date and Total Earned, don't appear to follow the description in your Original Post. Based on the OP, Quantity to Date should be the sum of Jan and Feb, which would be 0 for all Items. Please clarify.

Because the Estimate sheet will need to pull some data from sheet "Data Base" (Unit Price, Original Quantity), I'd suggest you have the Estimate sheet pull all data from Data Base and not reference Pivot Data at all. This is simpler, and it allows you to modify the PivotTable freely without affecting the Estimate sheet.

1. Will sheet Data Base always have just one row for each Item #?

2. On Estimate sheet, do you list all Item#'s from Data Base or just those that have earnings?

 

[Kimberlieh]

Jerry,

In my original post, I entered the quantities under the billed months, to show scenarios of skipped months. I didn't realize the formatting changed after posting by deleting my spaces. You are correct that the sums for Jan and Feb would be 0. If I only had to get the sum totals, I'd be good to go.

The conundrum is when I have more complicated searches. One example for instance, is when I have quantities in March, July, and November only, and need to reprint the August statement. The search values result are for all 12 months in quantity to date, total earned, and previously earned, but thanks to your early help, "This Estimate" is the correct amount.

While I don't foresee any need to modify the Data Table, pulling from the Data Base worksheet is fine by me. It gets a bit tricky since all our active projects are in the Data Base worksheet, resulting in numerous duplications of bid item numbers and descriptions, and making for longer and more complicated formulas. With the Pivot Table isolating the items by project, I thought it would simplify the formulas for the statement worksheet. After creating it, I was delighted to discover all I had to do was change the project name in the pivot table to automatically update the statement.

In answer to your questions,

1. No, sheet Data Base has many duplicate rows of "Item #", and description, but that said, yes, there is only one row for each item #, per project #.

2. On the Estimate sheet, all Items are listed, regardless of previous, current, or total earnings, and all relevant data is shown, even if 0. Incidentally, I prefer 0s to show as blanks.

Kimberlie

 

[Jerry Sullivan]

To simplify the complexity (and improve the speed) of formulas that pull data from Data Base, are you able to add a field to create a lookup key for the Job Number + Item#?

This would allow a simple Vlookup or Index-Match formulas to be used.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	Key	Job Number	Project Name	Bid Item Number	Bid Item Desccription
2	15031|10	15031	Fossil Heritage Trail (Fossil)	10	Mobilization
3	15031|30	15031	Fossil Heritage Trail (Fossil)	30	Temporary Signs In Place
4	15031|40	15031	Fossil Heritage Trail (Fossil)	40	Temporary Barricades, Type II
5	15031|50	15031	Fossil Heritage Trail (Fossil)	50	Temporary Barricades, Type III
6	15031|80	15031	Fossil Heritage Trail (Fossil)	80	Temporary Type 1 Fence
7	15031|470	15031	Fossil Heritage Trail (Fossil)	470	Unit Pavers
8	15999|10	15999	Other Project	10	Mobilization
9	15999|30	15999	Other Project	30	Temporary Signs In Place
Data Base

Cell Formulas
Range	Formula
A2	=B2&"|"&D2

The alternatives to adding this field include:

1. Using SUMIFS or Array formulas (slower) to read values from Data Base
2. Adding fields to the existing PivotTables for Unit Cost and Bid Qty.
3. Adding an additional PivotTable to allow the lookup of Unit Cost and Bid Qty from the Pivot Data sheet.

 

[Kimberlieh]

Jerry,

Yes, not only am I able to add the field combining the job number with the item number, it was already done in column A. I have a hyphen, with no additional spaces, instead of the vertical, as in your example, is that alright? (=B3&"-"&D3)

My hat's off to you if you can make the formula work. I couldn't figure out how to make it work with skipped months of billings, or billings after the queried month.

Kimberlie

 

[Jerry Sullivan]

Yes, a hyphen is fine. The main thing about picking a delimiter is to ensure that all possible keys will be unique. If your project numbers might have hyphens in them, you'd want to use something else.

A few more details, so you don't have to adjust the formulas for your specific ranges.

1. Are your headers on Data Base on row 2 or row 3?
2. I'd recommend using some named ranges to make the formulas simpler. Are you open to that, or would you prefer only range addresses be used?
3. Please clarify one more time the definitions of Quantity To-Date, Previously Earned, and This Estimate. This was a little unclear because there were no Prior Quantities, Jan, or Feb billings in the example screenshot.

This is my interpretation for a March statement, but parts of this don't align with your OP description.
Quantity To-Date: Qty for Prior Quantities, Jan, Feb, and Mar
Total Earned: Quantity to Date * Unit Price
Previously Earned: Qty for (Prior Quantities, Jan, Feb) * Unit Price
This Estimate: Total Earned-Previously Earned