"If" statements
bj thanks
There was a further error, a . before "GC" but changed for a , and a bracket
at the end of equation. Otherwise it works thanks
chuckee
"bj" wrote:
try instead for the second equation
=indirect("'Order Data'!"&lookup('Order
Data'!C7,{5,17,27},{"K","L","M"})&lookup('Order
Data'!C5,{"G","T"."GC"},{"38","11","29"})
I had left out a "!" and I did something wrong for the vlookup, but haven't
figured it out yet
"Chuckee" wrote:
bj
Looks a good formula but does not work !! Comes up with #Ref error. Is it me
or should i be adding something to the formula to make it work??
chuckee
"bj" wrote:
=indirect("'Order Data'!"&if('Order Data'!C7=5,"K",if(Order
Data'!C7=17,"L","M"))&if(Order Data'!C25="G","38",if(Order
Data'!C25="T","11","29")))
or
=indirect("'Order Data'"&Vlookup('Order
Data'!C7,{5,17,27},{"K","L","M"},2,0)&Vlookup('Ord er
Data'!C5,{"G","T"."GC"},{"38","11","29"},2,0)
The second one is easier to add more options.
"Chuckee" wrote:
Hi all,
I have a cell with the following "If" statements. Im aware that you can only
have 7 "IF" statements in a cell but how would i change what i have to a
working formula? I believe "Lookup" is an option but never used it before?
IF(AND('Order Data'!C25="G",'Order Data'!C7=5),'Order Data'!K38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=5),'Order Data'!K11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=5),'Order Data'!K29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=5),'Order Data'!K20,
IF(AND('Order Data'!C25="G",'Order Data'!C7=17),'Order Data'!L38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=17),'Order Data'!L11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=17),'Order Data'!L29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=17),'Order Data'!L20,
IF(AND('Order Data'!C25="G",'Order Data'!C7=27),'Order Data'!M38,
IF(AND('Order Data'!C25="T",'Order Data'!C7=27),'Order Data'!M11,
IF(AND('Order Data'!C25="GC",'Order Data'!C7=27),'Order Data'!M29,
IF(AND('Order Data'!C25="I",'Order Data'!C7=27),'Order
Data'!M20,""))))))))))))
Cheers
|