I have a pivot table that I read data off.
The formula used is as follows:
IF(ISERROR(GETPIVOTDATA('By Trust and Specialty'!$A$3,"'RespWL IP - "&$B$4&"' "&$A12&" 'Current Month' '"&$D$192&" Total' '"&E$11&" Resp'"),0,GETPIVOTDATA('By Trust and Specialty'!$A$3,"'RespWL IP - "&$B$4&"' "&$A12&" 'Current Month' '"&$D$192&" Total' '"&E$11&" Resp'")
Currently the D192 is a reference to the month and year, eg 200306. And you have a pick list to select the months data you want to reference.
But now the format of data has changed, now the pivot table does not hold all the months data on it. they are split into 12 pivot tables labelled 200304, 200305 etc.
How can I change the reference so it automatically picks up they data?
The formula used is as follows:
IF(ISERROR(GETPIVOTDATA('By Trust and Specialty'!$A$3,"'RespWL IP - "&$B$4&"' "&$A12&" 'Current Month' '"&$D$192&" Total' '"&E$11&" Resp'"),0,GETPIVOTDATA('By Trust and Specialty'!$A$3,"'RespWL IP - "&$B$4&"' "&$A12&" 'Current Month' '"&$D$192&" Total' '"&E$11&" Resp'")
Currently the D192 is a reference to the month and year, eg 200306. And you have a pick list to select the months data you want to reference.
But now the format of data has changed, now the pivot table does not hold all the months data on it. they are split into 12 pivot tables labelled 200304, 200305 etc.
How can I change the reference so it automatically picks up they data?