| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column of Data - Set Next Column Value Conditionally
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column of Data - Set Next Column Value Conditionally
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column of Data - Set Next Column Value Conditionally
Enter this in B1 and drag it down.
=If(A1="DDD","mule","donkey") "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Otto M. - Data Matching | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Linear Mode
