Good morning all,
I am in the process of redesigning a spreadsheet that has ballooned into a monstrosity. With that said I wanted to get some input on a better formula to calculate data on 2 and most likely 3 data points. Right now the spreadsheet forces the users to paste their data into one tab, change their data source to their pivot table then refresh it to get the data they need. Then they copy the pivot table and paste it back as values which is then referenced with a +GETPIVOTDATA formula in each and every cell.
The problem was that when a data point did not show up for a month or a new one showed up in the formulas would fail resulting in the formulas being edited every month to line up with the data. Below is the same data with private information changed to something more generic. The data points would the account, the department and the date. I am considering setting all of these up on a different tab to allow for maintenance and using a SUMIFS along with named ranges to bring the formual totals to the main worksheet. What I would like to know is there a better way to write the formula?
Formula sample:
=+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52010000,"Department",AB29,"account description","Production Direct Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52020000,"Department",AB29,"account description","Production Direct Labor Overtime")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52030000,"Department",AB29,"account description","Production Direct Contract Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051010,"Department",AB29,"account description","Production Direct Labor Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051015,"Department",AB29,"account description","Production Direct Labor Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051021,"Department",AB29,"account description","Production Direct Labor Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051022,"Department",AB29,"account description","Production Direct Labor FUI/SUI")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051040,"Department",AB29,"account description","Production Direct Labor Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051050,"Department",AB29,"account description","Production Direct Labor 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051060,"Department",AB29,"account description","Production Direct Labor Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60010100,"Department",AB29,"account description","Production Indirect Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60030000,"Department",AB29,"account description","Temporary Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051010,"Department",AB29,"account description","Production Indirect Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051015,"Department",AB29,"account description","Production Indirect Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051021,"Department",AB29,"account description","Production Indirect Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051030,"Department",AB29,"account description","Production Indirect Basic Life")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051040,"Department",AB29,"account description","Production Indirect Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051050,"Department",AB29,"account description","Production Indirect 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051060,"Department",AB29,"account description","Production Indirect Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051070,"Department",AB29,"account description","Production Indirect Bereavement")
You will see this does not line up with the pivot formula but you get the idea of what they did to solve their problem but I am sure you would agree not a good way to set it up.
I am in the process of redesigning a spreadsheet that has ballooned into a monstrosity. With that said I wanted to get some input on a better formula to calculate data on 2 and most likely 3 data points. Right now the spreadsheet forces the users to paste their data into one tab, change their data source to their pivot table then refresh it to get the data they need. Then they copy the pivot table and paste it back as values which is then referenced with a +GETPIVOTDATA formula in each and every cell.
The problem was that when a data point did not show up for a month or a new one showed up in the formulas would fail resulting in the formulas being edited every month to line up with the data. Below is the same data with private information changed to something more generic. The data points would the account, the department and the date. I am considering setting all of these up on a different tab to allow for maintenance and using a SUMIFS along with named ranges to bring the formual totals to the main worksheet. What I would like to know is there a better way to write the formula?
Formula sample:
=+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52010000,"Department",AB29,"account description","Production Direct Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52020000,"Department",AB29,"account description","Production Direct Labor Overtime")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52030000,"Department",AB29,"account description","Production Direct Contract Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051010,"Department",AB29,"account description","Production Direct Labor Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051015,"Department",AB29,"account description","Production Direct Labor Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051021,"Department",AB29,"account description","Production Direct Labor Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051022,"Department",AB29,"account description","Production Direct Labor FUI/SUI")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051040,"Department",AB29,"account description","Production Direct Labor Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051050,"Department",AB29,"account description","Production Direct Labor 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051060,"Department",AB29,"account description","Production Direct Labor Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60010100,"Department",AB29,"account description","Production Indirect Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60030000,"Department",AB29,"account description","Temporary Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051010,"Department",AB29,"account description","Production Indirect Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051015,"Department",AB29,"account description","Production Indirect Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051021,"Department",AB29,"account description","Production Indirect Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051030,"Department",AB29,"account description","Production Indirect Basic Life")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051040,"Department",AB29,"account description","Production Indirect Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051050,"Department",AB29,"account description","Production Indirect 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051060,"Department",AB29,"account description","Production Indirect Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051070,"Department",AB29,"account description","Production Indirect Bereavement")
You will see this does not line up with the pivot formula but you get the idea of what they did to solve their problem but I am sure you would agree not a good way to set it up.
