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)

Joel wrote:
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]
can this be done in a cell? and if so, what is the syntax for separateing the
exprressions ("IF(B8=j8,j9,0)")?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200706/1