Dynamic Getpivotdata in 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

 

[Dave Breitenbach]

would like to help but need more info. Its very hard to picture your data
from the getpivotdata formula. Can you add a small table of data and
describe in a sentence specifically what you're trying to accomplish?

 

[Debra Dalgleish]

You could enclose the entire GETPIVOTDATA formula in an IF function:

=IF(D1="ALL",GETPIVOTDATA("Loads",$A$3,"SHIPTO",A1,"MILL",B1),
GETPIVOTDATA("Loads",$A$3,"SHIPTO",A1,"MILL",B1,
"Product Line","Line1" ))