Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
I'm desperate I posted earlier about "If" statements that don't work but are correct. Now... | Excel Worksheet Functions | |||
How can I have multiple "If" statements in one formula? | Excel Discussion (Misc queries) | |||
Using "IF" statements...my problem | Excel Discussion (Misc queries) | |||
Linking two IF statements together | Excel Discussion (Misc queries) |