bj ...... a problem !!

When the value in C7 is changed from 5 to 17 or 27 the result = 0
I can only assume that the formula is not identifying the change in C7

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