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

 

[Kimberlieh]

Good Point, some jobs do have a hyphen, so the line would be smart.

1. my headers on the Data Base are on row 2
2. Named ranges are alright. I know how to create them, but I always forget to use them.
3. In spite of my poor explanation, I think you've nailed it. I think a better example may have been to use a statement period of September, and show quantities earned in March, July, September, and November.

Quantity to date: The sum of quantities prior to and including the query month
Total Earned: The sum of quantities prior to and including the query month * Unit Price
Previously Earned: The sum of quantities prior to query month * Unit Price
Quantity Earned this Period: Quantities earned for query month * Unit Price, (Total Earned-Previously Earned).

The same scenario would apply regardless of the query period and how many months contain earned quantities. You may not have noticed, but there is an additional column to the left of January. It contains the total from prior years, and those quantities must be included in the sum totals. (Someday I'll figure out how to count the number of statements).

 

[Jerry Sullivan]

I've populated the months you suggested with some random numbers.

Excel 2016 (Windows) 32 bit
	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	Prior Quantities	Jan Qty	Feb Qty	Mar Qty	Apr Qty	May Qty	Jun Qty	July Qty	Aug Qty	Sep Qty	Oct Qty	Nov Qty	Dec Qty
3	19	0	0	1	0	0	0	5	0	19	0	15	0
4	12	0	0	11	0	0	0	10	0	19	0	20	0
5	1	0	0	4	0	0	0	2	0	20	0	5	0
6	7	0	0	19	0	0	0	19	0	14	0	8	0
7	13	0	0	10	0	0	0	6	0	7	0	15	0
8	18	0	0	3	0	0	0	14	0	20	0	10	0
Data Base

Here's one way to develop formulas for the Statement. It relies heavily on named ranges and named formulas to simplify the worksheet formulas.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													Month
2						Contract No.		15031					Sep
3						Project Name:		Fossil Heritage Trail (Fossil)
4
5				Quantity	Unit	Total		Previously		THIS	Original	Contract
6	Item #	Description		To-Date	Price	Earned		Earned		ESTIMATE	Quantity	Amount
7	10	Mobilization		44	500	22,000		12,500		9,500	4	2,000
8	30	Temporary Signs In Place		52	14	728		462		266	545	7,630
9	40	Temporary Barricades, Type II		27	45	1,215		315		900	24	1,080
Estimate

Cell Formulas
Range	Formula
D7	=SUM(INDEX(DataBaseColumns,ThisDataBaseRow,PriorQtyDataCol):INDEX(DataBaseColumns,ThisDataBaseRow,StatementMonthDataCol))
E7	=VLOOKUP(ThisRowKey,DataBaseColumns,MATCH("Bid Price",DataBaseHeaders,0),0)
F7	=E7*D7
J7	=INDEX(DataBaseColumns,ThisDataBaseRow,StatementMonthDataCol)*$E7
K7	=VLOOKUP(ThisRowKey,DataBaseColumns,MATCH("Bid Qty",DataBaseHeaders,0),0)
L7	=E7*K7
H7	=F7-J7

Named Ranges
Name	Refers To	Cells
DataBaseColumns	='Data Base'!$A:$V
DataBaseHeaders	='Data Base'!$A$2:$V$2
PriorQtyDataCol	=COLUMN(PriorQtyHeader)
StatementMonth	=Estimate!$M$2
StatementMonthDataCol	=MATCH(StatementMonth &" Qty",DataBaseHeaders,0)
ThisDataBaseRow	=MATCH(ThisRowKey,INDEX(DataBaseColumns,0,1),0)
ThisRowKey	=StatementContractNo&"|"&Estimate!$A1

I've formatted the values not to show decimals only so that all columns could fit on one screenshot.

If you've created Names before, then you should be able to create the names using the list below the screenshot. The only one that is a little tricky is ThisRowKey since it is a relative reference. You must first select cell Estimate!A1 before adding that name.

 

[Jerry Sullivan]

One other note. For the Previously Earned field, I just subtracted This Estimate from Total Earned. You might want to add a checksum field off to the side to check that this difference equals the sum of the previous months qty * unit prices.

Excel 2016 (Windows) 32 bit
	O	P
5	Previously
6	Earned Check
7	12,500.00	OK
8	462.00	OK
9	315.00	OK
Estimate

Cell Formulas
Range	Formula
O7	=SUM(INDEX(DataBaseColumns,ThisDataBaseRow,PriorQtyDataCol):INDEX(DataBaseColumns,ThisDataBaseRow,StatementMonthDataCol-1))*$E7
P7	=IF(ABS(O7-H7)<0.00001,"OK","Error")

Named Ranges
Name	Refers To	Cells
DataBaseColumns	='Data Base'!$A:$V
PriorQtyDataCol	=COLUMN(PriorQtyHeader)
StatementMonth	=Estimate!$M$2
StatementMonthDataCol	=MATCH(StatementMonth &" Qty",DataBaseHeaders,0)
ThisDataBaseRow	=MATCH(ThisRowKey,INDEX(DataBaseColumns,0,1),0)

 

[Kimberlieh]

Jerry,

I'll get all the formulas entered and let you know how it goes.

Kimberlie

 

[Kimberlieh]

Jerry,

To make sure I didn't botch formulas, I copy/pasted your's into Name Manager, and selected "Estimate!A1" before pasting in the ThisRowKey one. Then I copy/pasted in your formulas. The formulas returned the error #NAME.

There are a few things to review, which may provide quick fixes.

1. What name did you give the header on the Data Base worksheet for the combination of job and item number? I have it as Job & Bid Item #s

a. It's hokie, but my computer doesn't seem to care for "..."|"...". No matter how many creative ways I tried to make it work, the right quotation mark was always changed to a star. I gave up, and changed the "|" to "Q".

2. The Estimate worksheet headers are on row 11 and the data is on row 12.
a. I noticed the row reference discrepancy in your example, and adjusted the formulas accordingly.
b. I moved the "Month" to M2.

3. By using the formula you provided from my first Forum post, "This Estimate" column works beautifully, so I'd like to leave it alone.

4. I've been testing each of the named ranges, and I am not able to isolate the problem. Do you have any ideas of what I can test?

Kimberlie

 

[Jerry Sullivan]

Kimberlie,

I can take a look at your file if you like. You could post it to a hosting site like box.com, or send me a PM if you'd prefer to send by email.