Dynamic Getpivotdata in Excel XP
 

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

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?

" wrote:

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

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" ))

wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html