"If" statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chuckee - 09 Feb 2007 13:22 GMT

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

Reply to this Message

Gary''s Student - 09 Feb 2007 13:51 GMT

In A1 thru B12 put:

G5    'Order Data'!K38
T5    'Order Data'!K11
GC5    'Order Data'!K29
I5    'Order Data'!K20
G17    'Order Data'!L38
T17    'Order Data'!L11
GC17    'Order Data'!L29
I17    'Order Data'!L20
G27    'Order Data'!M38
T27    'Order Data'!M11
GC27    'Order Data'!M29
I27    'Order Data'!M20

and then

=INDIRECT(VLOOKUP(C25&C7,A1:B12,2,0))

Signature

Gary's Student
gsnu200705

> Hi all,
>
[quoted text clipped - 17 lines]
>
> Cheers

Reply to this Message

Chuckee - 09 Feb 2007 15:20 GMT

Gary, Thanks for replying. Holding back on this option for the moment. May
use later

Thanks
chuckee

> In A1 thru B12 put:
>
[quoted text clipped - 36 lines]
> >
> > Cheers

Reply to this Message

Martin Fishlock - 09 Feb 2007 14:08 GMT

Hi:

I used two vlookups to get the row and the column the formula is quite long
but shorter than ifs but you need to tables:

=IF(ISNA(((VLOOKUP('Order Data'!C7,$B$23:$C$25,2,FALSE)&VLOOKUP('Order
Data'!C25,$B$17:$C$20,2,FALSE)))),"",INDIRECT((VLOOKUP('Order
Data'!C7,$B$23:$C$25,2,FALSE)&VLOOKUP('Order Data'!C25,$B$17:$C$20,2,FALSE))))

the tables were on a sheet at B17:C20 for the row:

G    38
T    11
GC    29
I    20

and B23:C25 for the column

5    K
17    L
27    M

I then used indirect to calculate the address and give me the value.

I put an if (isna()) to pick up bad values.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> Hi all,
>
[quoted text clipped - 17 lines]
>
> Cheers

Reply to this Message

Chuckee - 09 Feb 2007 15:19 GMT

Martin,

Thanks, holding back on using this option at the moment but thanks for the mo

chuckee

> Hi:
>
[quoted text clipped - 43 lines]
> >
> > Cheers

Reply to this Message

bj - 09 Feb 2007 14:39 GMT

=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('Order
Data'!C5,{"G","T"."GC"},{"38","11","29"},2,0)

The second one is easier to add more options.

> Hi all,
>
[quoted text clipped - 17 lines]
>
> Cheers

Reply to this Message

Chuckee - 09 Feb 2007 15:18 GMT

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

> =indirect("'Order Data'!"&if('Order Data'!C7=5,"K",if(Order
> Data'!C7=17,"L","M"))&if(Order Data'!C25="G","38",if(Order
[quoted text clipped - 27 lines]
> >
> > Cheers

Reply to this Message

bj - 09 Feb 2007 15:43 GMT

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

> bj
>
[quoted text clipped - 34 lines]
> > >
> > > Cheers

Reply to this Message

Chuckee - 09 Feb 2007 16:08 GMT

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

> try instead for the second equation
>
[quoted text clipped - 43 lines]
> > > >
> > > > Cheers

Reply to this Message

Chuckee - 09 Feb 2007 16:20 GMT

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

> try instead for the second equation
>
[quoted text clipped - 43 lines]
> > > >
> > > > Cheers

Reply to this Message