| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
I have the following formula and can not get it to work. Could someone have
a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
Can R3 be any other value besides 1,2,3,4 or 5?
Will it ever be empty? Biff "lccubb" wrote in message ... I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
TRY:
=IF(G3="Change",OFFSET('Data Validation'!C32,R3-1,0,1,1),IF(G3="New",OFFSET('Data Validation'!C26,R3-1,0,1,1),"")) In your formula you don't need the AND .. I haven't tested this! IF(G3="Change",IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),IF((G3="New",IF(R3=1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
Biff's reply:
I assumed (and I shouldn't have!) that R3 will always be 1 to 5. If not, we need to add extra logic in my formula. "Toppers" wrote: TRY: =IF(G3="Change",OFFSET('Data Validation'!C32,R3-1,0,1,1),IF(G3="New",OFFSET('Data Validation'!C26,R3-1,0,1,1),"")) In your formula you don't need the AND .. I haven't tested this! IF(G3="Change",IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),IF((G3="New",IF(R3=1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
a simpler equation might be something like
if(G3="Change",indirect("'Data Validation'!C3"&choose(r3,2,3,4,5,6)),if G3="New",indirect("'Data Validation'!C"&choose(R3,26,27,28,29,30)), "otherewise") you have an error around the first )))) section, you end the first if statement with the "") it does not see the the next if statement as an else also I do not know why you have the and() in there =IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),IF(AND(G3="New"),IF(R3=1,' Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,"")))))),"") "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
Yes R3 could be blank.
"lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
TRY:
=IF(R3="","",IF(G3="Change",OFFSET('Data Validation!'C32,R3-1,0,1,1),IF(G3="New",OFFSET('Data Validation!'C26,R3-1,0,1,1),""))) "lccubb" wrote: Yes R3 could be blank. "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
Try this:
=IF(OR(R3=0,R35),"",IF(G3="new",INDEX('data Validation'!C26:C30,R3,0),IF(G3="change",INDEX('da ta Validation'!C32:C36,R3,0),""))) Biff "lccubb" wrote in message ... Yes R3 could be blank. "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula help
Your correct. I guess I did not need the AND statement.
I have this formula in and now all I receive is a #VALUE! entry. "bj" wrote: a simpler equation might be something like if(G3="Change",indirect("'Data Validation'!C3"&choose(r3,2,3,4,5,6)),if G3="New",indirect("'Data Validation'!C"&choose(R3,26,27,28,29,30)), "otherewise") you have an error around the first )))) section, you end the first if statement with the "") it does not see the the next if statement as an else also I do not know why you have the and() in there =IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),IF(AND(G3="New"),IF(R3=1,' Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,"")))))),"") "lccubb" wrote: I have the following formula and can not get it to work. Could someone have a look please. Thank you in Advance. IF(AND(G3="Change"),IF(R3=1,'Data Validation'!C32,IF(R3=2,'Data Validation'!C33,IF(R3=3,'Data Validation'!C34,IF(R3=4,'Data Validation'!C35,IF(R3=5,'Data Validation'!C36,""))))),""),IF(AND(G3="New"),IF(R3 =1,'Data Validation'!C26,IF(R3=2,'Data Validation'!C27,IF(R3=3,'Data Validation'!C28,IF(R3=4,'Data Validation'!C29,IF(R3=5,'Data Validation'!C30,""))))),"") What I want is that if G3 = the word "Change" then look at another table and give me the results if R3 =1, 2, 3, 4, or 5. If G3 = the work "New" then look at another table and give be the results if R3 =1, 2, 3, 4, or 5. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
