#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "If" statements

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default "If" statements

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))

--
Gary's Student
gsnu200705


"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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 694
Default "If" statements

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((VLO OKUP('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.

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


"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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default "If" statements

=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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "If" statements

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "If" statements

Martin,

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

chuckee

"Martin Fishlock" wrote:

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((VLO OKUP('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.

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


"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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "If" statements


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

Thanks
chuckee

"Gary''s Student" wrote:

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))

--
Gary's Student
gsnu200705


"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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default "If" statements

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default "If" statements

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need more than 7 nested "IF" statements in Excel" James A Excel Discussion (Misc queries) 1 December 17th 06 03:02 AM
I'm desperate I posted earlier about "If" statements that don't work but are correct. Now... Marc Excel Worksheet Functions 2 April 5th 06 09:27 PM
How can I have multiple "If" statements in one formula? MikeL Excel Discussion (Misc queries) 5 January 15th 06 04:36 AM
Using "IF" statements...my problem [email protected] Excel Discussion (Misc queries) 1 December 1st 05 06:10 AM
Linking two IF statements together trixma Excel Discussion (Misc queries) 2 September 29th 05 06:07 AM


All times are GMT +1. The time now is 04:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"