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.

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.

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.

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.

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.

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.

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.

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.

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.