Calculations using Pivot table results over several months - not updating formula

[JoPublic]

Hi
I have a workbook for resource management that has pivot tables for Capacity and Planned/booked work.
I need to have a table that compares the capacity vs planned/booked time by resource type over 12+ months. This will show where there are resource demand issues.

When I add a formula to the table, capacity cell minus what is planned/booked, this is what the formula looks like. =GETPIVOTDATA("Sum of Nov-21",$A$118,"Job Role","Analyst/Programmer")-GETPIVOTDATA("Sum of Nov-21",$A$5,"Job Role","Analyst/Programmer")
When I drag it over to Dec 21 onwards - the formula is static, remains set on Nov 21 data.

Is there way to have the formula be more dynamic, so if you drag it across it will update the months and name headings? Is there a better way to compare in excel?

Thanks in advance for any advice - this is a time consuming piece of work that is needed regularly to report on. I have manually changed the dates in row 38, and row 39 is what happens when dragging across.

Jo

 

[jorismoerings]

Hi,

Are you sure this is the complete formula?
Do you have a separate pivot tables for each month of booked and each month of planned and if not it looks like a reference to the applicable month i missing in your formula.

 

[Alex Blakenburg]

GetPivot is really good if you want to pull a value from a pivot the position of which is likely to change eg a number for analyst programmer or a total of a certain combination.

If you never want to use it you can turn it off under File > Options > Formulas > Working with Formulas
> Use GetPivotData functions for PivotTable reference.

To by pass the GetPivot function just type in = and then type in the cell reference.
Or = and then select 2 cells and backspace to get rid of the one you don't want.

I am not sure I have what you are trying to do right but here are some options you could consider.

Book4
	J	K	L	M	N	O	P
23				Values
24			type	Sum of Nov-21	Sum of Dec-21	Sum of Jan-22
25			Clerk	300	700	300
26			Machine	300	300	500
27			Grand Total	600	1000	800
28
29
30	Get Pivot		Clerk	300	700	300
31	Straight Cell reference		Hard coded	300	700	300
32	Index Match		Clerk	300	700	300
33
Sheet1

Cell Formulas
Range	Formula
M30:O30	M30	=GETPIVOTDATA(SUBSTITUTE(M$24,"Sum of ",""),$L$23,"type",$L30)
M31:O31	M31	=M25
M32:O32	M32	=INDEX($L$24:$O$26,MATCH($L32,$L$24:$L$26,0),MATCH(M$24,$L$24:$O$24,0))

 

[JoPublic]

Hi,

Are you sure this is the complete formula?
Do you have a separate pivot tables for each month of booked and each month of planned and if not it looks like a reference to the applicable month i missing in your formula.

Hi

I have two pivots tables, one that is for capacity and the other on bookings. These are from separate spreadsheets. Both pivot tables on in the same worksheet which is where I have the table to - capacity minus booked time, to see what the over/under is for the month.

 

[jorismoerings]

Hi

I have two pivots tables, one that is for capacity and the other on bookings. These are from separate spreadsheets. Both pivot tables on in the same worksheet which is where I have the table to - capacity minus booked time, to see what the over/under is for the month.

Hi,

Could you show one or both pivot table using XL2BB?

 

[JoPublic]

Hi,

Could you show one or both pivot table using XL2BB?

? If only I understood what that meant. Sorry I am not techie.

 

[Alex Blakenburg]

joris means this:-

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.