Is there anyway to simplify this formula?
 

This is my current formula:

=SUM(IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula #",175100,"Group
#",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula #",175100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",1575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",1575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",275100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",275100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",2575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",2575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",375100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",375100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",3575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",3575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",475100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",475100,"Group #",Sheet1!C2))))

I have at least 7 different Formula # possibilities that can occur in my
pivot table for each Group #. Is there any way to create a formula using
wildcards to recognize the Formula # using the last five digits of the
formula #? Every time I try to insert the wildcard I get an error message.
I haven't been able to get it to work with either the "?" or "*" characters.

Is there anyway to simplify this formula?
 

I don't know of a way to use wildcards in a GETPIVOTDATA formula, but
you could set up a table with a list of Formula #s:

Formula# Data Calc
175100 * **

*For the Data formula, enter a GETPIVOTDATA formula that refers to the
formula entered in the first column, e.g.

=GETPIVOTDATA("Premix 1",$A$3,"Formula #",A2,"Group #",Sheet1!C2)

*For the Cal formula, test the result of the GETPIVOTDATA formula, e.g.

=IF(ISERROR(B2),0,B2)

Add a formula to total the entries in the Calc column.
This should be easier to maintain and decipher than having one giant
formula.

rancher fred wrote:
This is my current formula:

=SUM(IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula #",175100,"Group
#",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula #",175100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",1575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",1575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",275100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",275100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",2575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",2575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",375100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",375100,"Group
#",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",3575100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix 1",$A$3,"Formula
#",3575100,"Group #",Sheet1!C2))),IF(ISERROR(GETPIVOTDATA("Premix
1",$A$3,"Formula #",475100,"Group #",Sheet1!C2)),0,(GETPIVOTDATA("Premix
1",$A$3,"Formula #",475100,"Group #",Sheet1!C2))))

I have at least 7 different Formula # possibilities that can occur in my
pivot table for each Group #. Is there any way to create a formula using
wildcards to recognize the Formula # using the last five digits of the
formula #? Every time I try to insert the wildcard I get an error message.
I haven't been able to get it to work with either the "?" or "*" characters.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html