Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple values in one cell
OK, I'm a nurse new to Excel and here's what I need to do:
On one worksheet I've got a range of cells that form a table where a phone person puts an "X" in the second column if that row applies to the person she's talking to. I'll use "Mental Status" as an example: Alert x Oriented x Forgetful Confused Agitated Depressed x Now on another worksheet (that will be a form taken out into the field) I have a cell with the text "Mental Status:" What I want to do is have the cell just to the right of this be populated with all the words from the first table that have an "X" marked by them. In other words, in the example, it would look like: Mental Status: Alert Oriented Depressed So what formula can I use to accomplish this? Thanks so much, Sharon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple values in one cell
Sharon --
By Monday, a power user will come along with a more elegant solution, probably using an array formula. Just in case they don't here's a "brute force" approach that I tested, so I know it works: A B 1 Alert, X 2 Anxious, 3 Obnoxious, 4 Belligerent, X Here's the complicated, boring part. Remember to keep track of the parentheses, and note that you'll see a lot of double quotation marks - "". These mean that the formula is either finding nothing or putting nothing into a cell. A B 10 Mental Status: =concatenate(=if(B1<"",A1,""),if(b2<"",A2,"")) This formula says, "If you find anything in cell B1, get what you find in cell A1. Do the same with B2 and A2. Concatenate the two of them (which is why I put a comma & space after each in cells A1 and A2). Take this long sequence of text and put it in cell B10." You'd continue with B3, B4, B5, to get all the comments you want to make. Again, I'm SURE there's an easier way, I just don't know it. HTH Peter "Sharon Mann" wrote: OK, I'm a nurse new to Excel and here's what I need to do: On one worksheet I've got a range of cells that form a table where a phone person puts an "X" in the second column if that row applies to the person she's talking to. I'll use "Mental Status" as an example: Alert x Oriented x Forgetful Confused Agitated Depressed x Now on another worksheet (that will be a form taken out into the field) I have a cell with the text "Mental Status:" What I want to do is have the cell just to the right of this be populated with all the words from the first table that have an "X" marked by them. In other words, in the example, it would look like: Mental Status: Alert Oriented Depressed So what formula can I use to accomplish this? Thanks so much, Sharon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple values in one cell
On Sat, 21 Jan 2006 17:20:44 -0800, "Sharon Mann" wrote:
OK, I'm a nurse new to Excel and here's what I need to do: On one worksheet I've got a range of cells that form a table where a phone person puts an "X" in the second column if that row applies to the person she's talking to. I'll use "Mental Status" as an example: Alert x Oriented x Forgetful Confused Agitated Depressed x Now on another worksheet (that will be a form taken out into the field) I have a cell with the text "Mental Status:" What I want to do is have the cell just to the right of this be populated with all the words from the first table that have an "X" marked by them. In other words, in the example, it would look like: Mental Status: Alert Oriented Depressed So what formula can I use to accomplish this? Thanks so much, Sharon Assumption: Your mental status conditions and 'x's are in Sheet1!A1:B6 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr 2. Try the following **array** formula. (To enter an array-formula, after typing or pasting it into the formula bar, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula): =TRIM(MCONCAT(IF(Sheet1!B1:B6="x",Sheet1!A1:A6,"") ," ")) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
a unique cell value returns multiple cell values from another shee | Excel Worksheet Functions | |||
Extract one numerical value from single cell with multiple values? | Excel Worksheet Functions | |||
Splitting multiple cell contents containing values at end | Excel Worksheet Functions |