Getpivotdata Excel XP

[valglad]

Folks


I urgently need help with this function.


I'm trying to make this function dynamic, i.e.


depending on some condition, make some members disappear or otherwise.


=getpivotdata("Loads",'Pivot Plan'!$a$3,"SHIPTO",A1,"MILL",B1 &
IF(D1="ALL","",","&"PRODUCT LINE")&IF(D1="ALL","",","&"LINE1"))


If cell D1 has value "ALL" then both "Product Line" and its value
("LINE1") are not used, if otherwise they should be used.


I've wasted hours playing with syntax, such as putting commas into
separate "if" statements, nothing works.


Appreciate any help.


Thanks

 

[Debra Dalgleish]

Did you try the suggestion that I posted yesterday?


http://groups.google.ca/groups?&[email protected]

 

[valglad]

Hi Debra

Thanks for your help.

Unfortunately, the getpivotdata function I'm working with is a part of
a complex calculation, so using the additional "if" will push it
beyound 1,024 characters. The idea was to allow users to punch a
product line value in a cell - either a specific line or a word "ALL"
that would bring data for all product lines, hence the need to hide the
Product line member.
I can use the pre-XP syntax however, it is more difficul to read when
dealing with a complex formula, since the new (is it post XL97?) syntax
has field names, so when the function is "dynamic" (cell reference
based) it becomes important.

I played with using cell references for field names and items and it
works fine, however when I need to eliminate commas since the Product
line member is not needed that's when it all falls apart.

Anyway, thanks again and if you can think of something please let me
know.

 

[Debra Dalgleish]

You could repeat one of the other fields if D! contains ALL, e.g.:

=GETPIVOTDATA("Loads",$A$3,"SHIPTO",$A$1,"MILL",$B$1,IF($D$1="ALL","ShipTo","PRODUCT
LINE"),IF($D$1="ALL",$A$1,"LINE1" ))

 

[valglad]

Hey thanks very much.

I feel inadequate now, how come I couldn't think of such a simple and
elegant solution.


Thanks again. This solved my problem entirely.

SHAWN

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it solved the problem.