You cannot do this with a function since a cell cannot have BOTH its own
value and a formula that changes it.
You could use a 'helper' column. In C1 for example, we could have
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B100,2,FALSE),B1,
VLOOKUP(A1,Sheet2!A1:B100,2,FALSE))
The formula looks at A1,attempts to locate its value in a table on Sheet2;
if it is not found then C1 gets B1's value otherwise a value from the table
is used.

I suppose you could also hid column B.
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cecil" wrote in
message ...

Column A contains text data ... unlimited set of values. Column B
contains a limited set of values, depending on the value of column A.
The Column B values are pre-set by another program. I would like to
_change_ the value of a column B cell if the column A value equals a
certain value. Otherwise the column B value should stay as is. How
can I do that?

Example Data Set
COL-A COL-B
AAA horse
BBB cow
CCC donkey
DDD dog
BBB cow
MMM cat
Situation If COL-A value = DDD, would like to change value in COL-B
from donkey to mule


--
Cecil
------------------------------------------------------------------------
Cecil's Profile:
http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=542932