Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data Entry Formula based on IF or something Better
Hi All,
I have a situation where I need to fill Column B with Text Depending on what Text is in Column A eg Column A Column B 1 GREAPPLES GREEN Products 2 PEARSGREEN GREEN Products 3 GreenGarbage GREEN Products In other words I would be using GRE as the condition in Col A for a product to classed as GREEN Products I would be grateful if someone can point me in the right direction. Thank you Edwin |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data Entry Formula based on IF or something Better
This works for the *one* example you gave.
=IF(ISNUMBER(SEARCH("Gre",A1)),"Green Products","") Leaving Column B blank if it doesn't find "Gre", BUT ... don't you have *other* text to look for? A different formula would be called for if you have a large datalist to look for. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Edwin Mashiringwani" wrote in message ... Hi All, I have a situation where I need to fill Column B with Text Depending on what Text is in Column A eg Column A Column B 1 GREAPPLES GREEN Products 2 PEARSGREEN GREEN Products 3 GreenGarbage GREEN Products In other words I would be using GRE as the condition in Col A for a product to classed as GREEN Products I would be grateful if someone can point me in the right direction. Thank you Edwin |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data Entry Formula based on IF or something Better
Thank you RD for your quick response. The example formula you suggested does
work. As it happens I have other text where your suggested formula would look like this =IF(ISNUMBER(SEARCH("Red",A1)),"Red Products","") Is there another formula I can use that recognise that if "Red" is found anywhere in Column A , the result in Column B would be " Red Products" , as well as handling "Gre" in Col A to have a result of "Green Products" and other variations ? Thank you in advance Edwin "RagDyeR" wrote in message ... This works for the *one* example you gave. =IF(ISNUMBER(SEARCH("Gre",A1)),"Green Products","") Leaving Column B blank if it doesn't find "Gre", BUT ... don't you have *other* text to look for? A different formula would be called for if you have a large datalist to look for. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Edwin Mashiringwani" wrote in message ... Hi All, I have a situation where I need to fill Column B with Text Depending on what Text is in Column A eg Column A Column B 1 GREAPPLES GREEN Products 2 PEARSGREEN GREEN Products 3 GreenGarbage GREEN Products In other words I would be using GRE as the condition in Col A for a product to classed as GREEN Products I would be grateful if someone can point me in the right direction. Thank you Edwin |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data Entry Formula based on IF or something Better
Hi Edwin
Perhaps you could modify the following array formula to suit your needs {=CHOOSE(MAX((ISNUMBER(SEARCH ({"Red","Green","Yellow"},A21))*{1,2,3}))+1 ,"No","Red","Green","Yellow")& " Product"} Use Ctrl+Shift+Enter (CSE) to commit on Edit the formula rather than just Enter. Do not type the curly braces { } yourself, Excel will enter them when you use CSE You can extend the list "Red","Green" in both sets of data, but you need to add to the 1,2,3 for each addition you make to the range. The "No" at the beginning of the second range, is to deal with the case where none of the colours are found. -- Regards Roger Govier "Edwin Mashiringwani" wrote in message ... Thank you RD for your quick response. The example formula you suggested does work. As it happens I have other text where your suggested formula would look like this =IF(ISNUMBER(SEARCH("Red",A1)),"Red Products","") Is there another formula I can use that recognise that if "Red" is found anywhere in Column A , the result in Column B would be " Red Products" , as well as handling "Gre" in Col A to have a result of "Green Products" and other variations ? Thank you in advance Edwin "RagDyeR" wrote in message ... This works for the *one* example you gave. =IF(ISNUMBER(SEARCH("Gre",A1)),"Green Products","") Leaving Column B blank if it doesn't find "Gre", BUT ... don't you have *other* text to look for? A different formula would be called for if you have a large datalist to look for. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Edwin Mashiringwani" wrote in message ... Hi All, I have a situation where I need to fill Column B with Text Depending on what Text is in Column A eg Column A Column B 1 GREAPPLES GREEN Products 2 PEARSGREEN GREEN Products 3 GreenGarbage GREEN Products In other words I would be using GRE as the condition in Col A for a product to classed as GREEN Products I would be grateful if someone can point me in the right direction. Thank you Edwin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to fill a cell based on an entry in one of two worksheets | Excel Worksheet Functions | |||
Excel formula timestamp for a data entry of a referenced cell | Excel Worksheet Functions | |||
Display data from cells based on a formula | Excel Discussion (Misc queries) | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |