ref to pivot table "formula too long" 1

[TomYC]

I am summing up discrete portions of a pivot table, meaning the value of a cell on one sheet is adding one GETPIVOTDATA value from another worksheet, to another, to another, etc. I'll attach a sample of a formula that is turning out to be too long, or rather is if I want to add one more item to the formula. I can't use SUMPRODUCT or INDEX/MATCH because my pivot table doesn't (won't?) carry down the identifying terms on which I could match (in my exampe, these would be things like the Department No. and the Job Code, Department Description).
So my question is, how can I "extend" the formula length, by some trick? Perhaps the answer is in re-presenting the pivot table so I can use SUMPRODUCT, etc.?

Here's an example of a current cell value:
=GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","101067","JobCd Descr","RN (GC,MN)","Fiscal Year",2009)+GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","101084","JobCd Descr","RN - PD","Fiscal Year",2009)+GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","101071","JobCd Descr","Asst Mgr, Nurse","Fiscal Year",2009)+GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","101039","JobCd Descr","RN (LJ)","Fiscal Year",2009,"Month",5)+GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","101063","JobCd Descr","Perioperative RN","Fiscal Year",2009)+GETPIVOTDATA("Worked Hours",'By Month'!$A$5,"Department","10611020","Dept Descr","NRSG-Ctu","JobCd","104650","JobCd Descr","Asst Dir, Cardiothoracic Surg","Fiscal Year",2009)

 

[SkipVought]

Could you please post an example of your SOURCE DATA that demonstrates why, " I can't use SUMPRODUCT or INDEX/MATCH because my pivot table doesn't (won't?) carry down the identifying terms on which I could match."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I would attach a snapshot of the pivot table if I knew how!
The pivot table that comes to me from elsewhere (PeopleSoft I presume) appears as staggered data, such as:
Department
Dept Code
Job Desc
Job Code
Workd Hrs, etc. and this gives, e.g.:
NRS-PD 0020 RN 067 998.90
NP 071 247.50
PA 052 141.50

In other words, the Department Description and Dept. Codes do not carry down or otherwise appear on each line in the pivot table. In my example I could do a sumproduct, etc., on the Job Desc and Job Code and then find the Worked hours, but need as well to separate out Departments & Dep Code columns. Perhaps there is a simple way to repeat these items down the spreadsheet, enabling more or less simple coding? I don't know; but I certainly cannot just drag or copy them down, as the pivot table logic doesn't permit that.

 

[SkipVought]

Use MS Query or Advanced Filter to return a qunique list of the Department Description and Dept. Codes.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Use MS Query or Advanced Filter to return a unique list of the Department Description and Dept. Codes.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks, Skip--
The problem I have with that, as I begin to explore MS-Query is that it presumes what I do not in fact have at present, i.e. 'tables' with no gaps or blank fields. If I had a way to get to such tables (perhaps by decomposing the pivot table--how?) I could use sumproduct and wouldn't need MS-Query in the first place, wouldn't I? I think my next step must be to rework the pivot table that comes to me, no?
T.

 

[SkipVought]

I thot you had the source data.

What would you use SUMPRODUCT on, if you did not have source data?

I am confused!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

In other words, the Department Description and Dept. Codes do not carry down or otherwise appear on each line in the pivot table.

Convert your pivot table to a data table like this:

1.Select the table
2. Convert it to values
Copy, PasteSpecial, Values
3. Select the area containing the staggered data (the pivot columns)
4. Now just select the blank cells within that selection
Edit, Goto, Special, Blanks
5. Enter a formula into all of these cells to get the value from the cell above
=[UpArrow] Ctrl-Enter (hold Ctrl while you press enter)
6. Select the entire table area and convert it to values
Ctrl-A
Edit, PasteSpecial, Values



Gavin

 

[TomYC]

Thank you Gavin!
For that work around, which is what I needed help on, until such time as I can find a way to de-construct the pivot table and use my other logical rout. Now I'll go try it---
T

 

[Gavona]

Glad to have been of some help. Some further ideas:

In terms of shortening your formula I would suggest removing some of the references to superfluous columns. Your GetPivotData refers to both Dept code and Dept Description, to both JobCd and jobCd Description.
To do this you may need to copy the pivot table and then remove those excess columns.

You could also do things like put "Worked Hours" into a separate cell and then refer to the cell in the formula:
=GETPIVOTDATA("Worked Hours",'B.......... would become
=GETPIVOTDATA(A1,'B.........

And since your formula is simply summing discrete portions you could split it into two formulae summing different bits and then a third to add the two result cells.

Gavin