testing data in above cell, and choosing column to copy from

[OTWarrior via OfficeKB.com]

i have a cell which wil set a value (5, 10, ETC). and depending on this value,
chnage the boxes below it.
i currenlty have:

=IF(B8=G8,G9,0)

which changes the cell is one criteria is met. however i want

=IF(B8=G8,G9,0) or IF(B8=j8,j9,0) or IF(B8=h8,h9,0)

can this be done in a cell? and if so, what is the syntax for separateing the
exprressions ("IF(B8=j8,j9,0)")?

 

[Guest]

Your formula has conflicting requirments. The data that gets displayed in
the cell must be independant.
B8 can equal G8, H8, and J8 at the same time. What do you want display in
this case.

 

[OTWarrior via OfficeKB.com]

how is it conflicting?, besides i figured it out myself, and it is

=IF(B8=G8,G9,IF(B8=H8,H9,IF(B8=I8,I9,IF(B8=J8,J9,0))))

to make it easier to view it is
=IF(B8=G8,G9, IF(B8=H8,H9, IF(B8=I8,I9, IF(B8=J8,J9,
0))))

Your formula has conflicting requirments. The data that gets displayed in
the cell must be independant.
B8 can equal G8, H8, and J8 at the same time. What do you want display in
this case.

i have a cell which wil set a value (5, 10, ETC). and depending on this value,
chnage the boxes below it.

[quoted text clipped - 8 lines]

can this be done in a cell? and if so, what is the syntax for separateing the
exprressions ("IF(B8=j8,j9,0)")?

 

[Guest]

without knowing what is in each column you can't tell if what you are doing
is logically correct. Looking at whatt you are doing from a mathematically
point of view it is something you want to avoid except under very controled
situations.

One situation would be

Col H Col I Col J
First Name Last Name Color Hair


Does it make sense that column B would have either the first name, last
name, or color of hair.


Second situation would be colums H-J are would have First names?

Col H Col I Col J
8 Bob Ed Bob
9 22 33 44

Bob is in both in column H and J. Is the number you want from row 9 the 22
or the 44. this is the conflicting situation. If row 9 column H & J both
contained 22 then there is no problem. Your formula will take the value in
column H before the one in column J.

how is it conflicting?, besides i figured it out myself, and it is

=IF(B8=G8,G9,IF(B8=H8,H9,IF(B8=I8,I9,IF(B8=J8,J9,0))))

to make it easier to view it is
=IF(B8=G8,G9, IF(B8=H8,H9, IF(B8=I8,I9, IF(B8=J8,J9,
0))))

Your formula has conflicting requirments. The data that gets displayed in
the cell must be independant.
B8 can equal G8, H8, and J8 at the same time. What do you want display in
this case.

i have a cell which wil set a value (5, 10, ETC). and depending on this value,
chnage the boxes below it.

[quoted text clipped - 8 lines]

can this be done in a cell? and if so, what is the syntax for separateing the
exprressions ("IF(B8=j8,j9,0)")?

 

[OTWarrior via OfficeKB.com]

Ah, I see what you are saying now, yes I had seen that problem, and the way i
worked around it was the validation field has 8 selections (All unique words),
and all of the cells will refernece that top cell, thus calling the correct
data.

=IF(B8=G8,G9, IF(B8=H8,H9,0))

in this case it doesn't matter if G8 and G12 have the same values, as the
criteria to check is always B8, which will be a unique reference (such as "1
pending"; "2 Complete", etc).

The only downside to the way I have done this is the maxium number of
criteria you can have is 8 (9 if you include the last false). I am guessing
that is when excel runs out of available colours to outline the referred
cells [luckily, there are only 8 sheets my colleague is using, plus
a 9th one for totals, so it works out ok in this situation)

without knowing what is in each column you can't tell if what you are doing
is logically correct. Looking at whatt you are doing from a mathematically
point of view it is something you want to avoid except under very controled
situations.

One situation would be

Col H Col I Col J
First Name Last Name Color Hair

Does it make sense that column B would have either the first name, last
name, or color of hair.

Second situation would be colums H-J are would have First names?

Col H Col I Col J
8 Bob Ed Bob
9 22 33 44

Bob is in both in column H and J. Is the number you want from row 9 the 22
or the 44. this is the conflicting situation. If row 9 column H & J both
contained 22 then there is no problem. Your formula will take the value in
column H before the one in column J.

how is it conflicting?, besides i figured it out myself, and it is

[quoted text clipped - 14 lines]

 

[OTWarrior via OfficeKB.com]

oops, wrong line, it was actually:
IF(B8="1 pending",G9, IF(B8="2 complete",H9,0))