Column of Data - Set Next Column Value Conditionally

[Cecil]

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

[Bernard Liengme]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Gary]

Enter this in B1 and drag it down.

=If(A1="DDD","mule","donkey")


"Cecil" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>