I am trying to figure out how the person who created a pivot table in a workbook (and is unavailable to ask directly) created what appears to be a calculated column called "PO Balance".
The database which feeds the pivot table contains a bunch of records representing purchase order transactions. For simplicity there are 2 "types" of records in the database: "PO" records which contain details of the Purchase Order by types of expense (consulting fees, T&E expenses). "Actual" records contain details of Invoices presented for payment against the purchase orders, with amounts for either 'consulting fees, or 'T&E expenses' or both. Generally there are many more "Actual" records (representing invoices) than "PO" records.
There is only one value field which is simply called "Amount". All records are entered with positive values in the 'Amount' field whether they are "PO" records or "Actual" records.
For simplicity the pivot table is constructed with the following Row labels: PO#, Exps Type (consulting fees, T&E expenses). The Column label area of the Pivot table results initially in 2 columns of numbers: the aggregate of the Amounts of the "PO" records and the aggregate of the Amounts of the "Actual" records.
An example of what the pivot table looks follows (including the calculated column):
Sum of Amount Record Type
PO# Exps Type PO Actual PO Balance
1234 Consulting fees 15,000 8,950 6,050
T&E Expenses 2,000 2,150 -150
1234 Total 17,000 11,100 5,900
My apologies, I don't know how (or if) to "tab" out the columnar values so they line up.
When I click on a value in the "PO Balance" column I see the following in the Formula bar: =PO-Actual. The PO Balance value on the "1234 Total" line is simply the numerical value 5900 in the above example.
I cannot, for the life of me, figure out how the originator of this pivot table accomplished this. I would like to know how this is done as I would like to use this technique in other situations.
Thank you,
Bill Freund
The database which feeds the pivot table contains a bunch of records representing purchase order transactions. For simplicity there are 2 "types" of records in the database: "PO" records which contain details of the Purchase Order by types of expense (consulting fees, T&E expenses). "Actual" records contain details of Invoices presented for payment against the purchase orders, with amounts for either 'consulting fees, or 'T&E expenses' or both. Generally there are many more "Actual" records (representing invoices) than "PO" records.
There is only one value field which is simply called "Amount". All records are entered with positive values in the 'Amount' field whether they are "PO" records or "Actual" records.
For simplicity the pivot table is constructed with the following Row labels: PO#, Exps Type (consulting fees, T&E expenses). The Column label area of the Pivot table results initially in 2 columns of numbers: the aggregate of the Amounts of the "PO" records and the aggregate of the Amounts of the "Actual" records.
An example of what the pivot table looks follows (including the calculated column):
Sum of Amount Record Type
PO# Exps Type PO Actual PO Balance
1234 Consulting fees 15,000 8,950 6,050
T&E Expenses 2,000 2,150 -150
1234 Total 17,000 11,100 5,900
My apologies, I don't know how (or if) to "tab" out the columnar values so they line up.
When I click on a value in the "PO Balance" column I see the following in the Formula bar: =PO-Actual. The PO Balance value on the "1234 Total" line is simply the numerical value 5900 in the above example.
I cannot, for the life of me, figure out how the originator of this pivot table accomplished this. I would like to know how this is done as I would like to use this technique in other situations.
Thank you,
Bill Freund
Last edited:
