dukeofscouts
Board Regular
- Joined
- Jan 19, 2009
- Messages
- 146
The following formula servers to act as a dynamic data extract, similar to AUTO-FILTER.
Every time I try to enter the formula I get an open box that is titled: Update Values: Data'
I've gone over the formula again and again, but I don't see where it is separating the address 'Raw Data', or why it would open a text box like this.
Does any one see what I'm missing?
=INDEX(OFFSET('Raw Data'!A2,0,0,COUNTA('Raw Data'!$A:$A),1),SMALL(
IF(Table4[Food Group]&$A$4,
if(Table4[Item]&$B$4,
if(Table4[Serving Size]&$C$4,
if((Table4[Grain]/MAX(OFFSET('Raw Data'!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$D$4,
if((Table4[Vegetable]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$E$4,
if((Table4[Fruit]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$F$4,
if((Table4[Dairy]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$G$4,
if((Table4[Protein]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$H$4,
if((Table4[Calories]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$I$4,
if((Table4[Protein2]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$J$4,
if((Table4[Carbohydrate]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$K$4,
if((Table4[Total Fiber]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$L$4,
if((Table4[Total Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$M$4,
if((Table4[Saturated Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$N$4,
if((Table4[Monounsaturated fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$O$4,
if((Table4[Polyunsaturated Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$P$4,
if((Table4[Omega 6]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Q$4,
if((Table4[Omega 3]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$R$4,
if((Table4[Cholesterol]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$S$4,
if((Table4[A]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$T$4,
if((Table4[[C ]]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$U$4,
if((Table4[E]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$V$4,
if((Table4[Thiamin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$W$4,
if((Table4[Riboflavin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$X$4,
if((Table4[Niacin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Y$4,
if((Table4[Folate]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Z$4,
if((Table4[B6]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AA$4,
if((Table4[b12]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AB$4,
if((Table4[Calcium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AC$4,
if((Table4[Phosphorus]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AD$4,
if((Table4[Magnesium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AE$4,
if((Table4[Iron]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AF$4,
if((Table4[Zinc]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AG$4,
if((Table4[Selenium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AH$4,
if((Table4[Potassium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AI$4,
if((Table4[Sodium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AJ$4,
ROW(INDIRECT("1:"&COUNTA('Raw Data'!$A:$A))))))))))))))))))))))))))))))))))))))),ROWS($A$8:A8)))
Every time I try to enter the formula I get an open box that is titled: Update Values: Data'
I've gone over the formula again and again, but I don't see where it is separating the address 'Raw Data', or why it would open a text box like this.
Does any one see what I'm missing?
=INDEX(OFFSET('Raw Data'!A2,0,0,COUNTA('Raw Data'!$A:$A),1),SMALL(
IF(Table4[Food Group]&$A$4,
if(Table4[Item]&$B$4,
if(Table4[Serving Size]&$C$4,
if((Table4[Grain]/MAX(OFFSET('Raw Data'!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$D$4,
if((Table4[Vegetable]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$E$4,
if((Table4[Fruit]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$F$4,
if((Table4[Dairy]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$G$4,
if((Table4[Protein]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$H$4,
if((Table4[Calories]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$I$4,
if((Table4[Protein2]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$J$4,
if((Table4[Carbohydrate]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$K$4,
if((Table4[Total Fiber]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$L$4,
if((Table4[Total Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$M$4,
if((Table4[Saturated Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$N$4,
if((Table4[Monounsaturated fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$O$4,
if((Table4[Polyunsaturated Fat]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$P$4,
if((Table4[Omega 6]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Q$4,
if((Table4[Omega 3]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$R$4,
if((Table4[Cholesterol]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$S$4,
if((Table4[A]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$T$4,
if((Table4[[C ]]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$U$4,
if((Table4[E]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$V$4,
if((Table4[Thiamin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$W$4,
if((Table4[Riboflavin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$X$4,
if((Table4[Niacin]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Y$4,
if((Table4[Folate]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$Z$4,
if((Table4[B6]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AA$4,
if((Table4[b12]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AB$4,
if((Table4[Calcium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AC$4,
if((Table4[Phosphorus]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AD$4,
if((Table4[Magnesium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AE$4,
if((Table4[Iron]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AF$4,
if((Table4[Zinc]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AG$4,
if((Table4[Selenium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AH$4,
if((Table4[Potassium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AI$4,
if((Table4[Sodium]/MAX(OFFSET(‘Raw Data’!$A$2,0,MATCH($A$2,$A$3:$AJ$3,0),1),COUNTA('Raw Data'!$A:$A),1))&$AJ$4,
ROW(INDIRECT("1:"&COUNTA('Raw Data'!$A:$A))))))))))))))))))))))))))))))))))))))),ROWS($A$8:A8)))