how do I build a formula in a pivot table such that when I 'fill right' it will adjust appropriately for each column. NOTE: I have two levels of values in the row headings. The formula I am using is:



=(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",30)*$A$6)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",45)*$A$7)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",60)*$A$8)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",75)*$A$9)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",90)*$A$10)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",180)*$A$12)+(GETPIVOTDATA("Count of Clinicians",$A$3,"MONTH","Feb","DURATION (minutes)",960)*$A$13)


My pivot table looks like this:

Feb
Duration Count of Clinicians Count of PCPs Count of MAs
30
45 1 1 1
60
75 1
90 1
180
960

135 135
Hours by Title 2.25

I hate to grovel, but I'm truly stuck here and could really use some insights. If this is something that should go in a more advanced Forum, I'm happy to do that. I just don't know where to find such a Forum.

many thanks,

Jo Lynn

Perhaps post a sample sheet ( click Go advanced - Manage attachments) showing what you have, expected results and what goes wrong.
The kind of lengthy formula you posted is hard to grasp

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Please find the attached raw data and pivot table. I built a formula in cell B14 on the Hours by Title sheet. I want to use 'fill right' to apply the same formula across row 14. However, when I do, it continues to use 'count of clinicians' (or column B) in the formula instead of automatically using the next column over. This results in incorrectly showing the same result from B14, even if the values in column C or D should be generating a different result.

Attached Files

For MS Forums.xlsx (33.1 KB, 8 views)

The pivot table is calculating correctly. Data can be found in every cell in row 27 so the pivot table is showing 1 for every heading. I have highlighted the row of data in the attached copy of your workbook.

Attached Files

copy of For MS Forums.xlsx (32.6 KB, 13 views)

Thanks for your reply. The row that is not calculating correctly is row 14 in the sheet Hours by Title Q1. In column C, for example, it should no longer use the data in cells B6-B12 (multiplied by the values in A6-A12), but should instead be using values in C6-C12, multiplied by the values in A6-A12.

Does anyone have a solution that I am missing? Please see last post. The row that is not calculating correctly is row 14 in the sheet Hours by Title Q1. In column C, for example, it should no longer use the data in cells B6-B12 (multiplied by the values in A6-A12), but should instead be using values in C6-C12, multiplied by the values in A6-A12.