Formula help

[Guest]

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.

 

[T. Valko]

Can R3 be any other value besides 1,2,3,4 or 5?

Will it ever be empty?

Biff

 

[Guest]

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

 

[Guest]

Re: 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.

 

[Guest]

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

 

[Guest]

Yes R3 could be blank.

 

[Guest]

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

 

[T. Valko]

Try this:

=IF(OR(R3=0,R3>5),"",IF(G3="new",INDEX('data
Validation'!C26:C30,R3,0),IF(G3="change",INDEX('data
Validation'!C32:C36,R3,0),"")))

Biff

 

[Guest]

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.