Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show all items with an "x" next to them
On Sheet 1, I have several check boxes spread out across several rows and
columns. Next to each check box is a description - to make it easy I'll call them "shoes", "hats", "gloves", and "rutabagas". I'm looking for a formula I can use on a separate sheet that will show all of the checked items separated by commas. For example, if someone placed checks next to hats, rutabagas, and gloves, the formula on the next page would display "hats, rutabagas, gloves". Is there such a formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show all items with an "x" next to them
If your checkboxes are linked to A1:A4 on Sheet1 you could use
=TRIM(SUBSTITUTE(IF(Sheet1!A1=TRUE," shoes ","")&IF(Sheet1!A2=TRUE," hats ","")&IF(Sheet1!A3=TRUE," gloves ","")&IF(Sheet1!A4=TRUE," rutabagas ","")," ",", ")) The IF statements are not nested in each other, so you are not limited. Leaving the spaces around each " description " leaves double spaces between them. SUBSTITUTE changes the double spaces to ", " TRIM eliminites the extraneous spaces at the beginning and end. You don't the space before the first ("shoes " would work) or after the last (" rutabegas" would work) but you need the TRIM anyway in case the first and/or last is not checked. "BCBC" wrote in message ... On Sheet 1, I have several check boxes spread out across several rows and columns. Next to each check box is a description - to make it easy I'll call them "shoes", "hats", "gloves", and "rutabagas". I'm looking for a formula I can use on a separate sheet that will show all of the checked items separated by commas. For example, if someone placed checks next to hats, rutabagas, and gloves, the formula on the next page would display "hats, rutabagas, gloves". Is there such a formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show all items with an "x" next to them
Thanks Bob! That worked!
"Bob Tarburton" wrote: If your checkboxes are linked to A1:A4 on Sheet1 you could use =TRIM(SUBSTITUTE(IF(Sheet1!A1=TRUE," shoes ","")&IF(Sheet1!A2=TRUE," hats ","")&IF(Sheet1!A3=TRUE," gloves ","")&IF(Sheet1!A4=TRUE," rutabagas ","")," ",", ")) The IF statements are not nested in each other, so you are not limited. Leaving the spaces around each " description " leaves double spaces between them. SUBSTITUTE changes the double spaces to ", " TRIM eliminites the extraneous spaces at the beginning and end. You don't the space before the first ("shoes " would work) or after the last (" rutabegas" would work) but you need the TRIM anyway in case the first and/or last is not checked. "BCBC" wrote in message ... On Sheet 1, I have several check boxes spread out across several rows and columns. Next to each check box is a description - to make it easy I'll call them "shoes", "hats", "gloves", and "rutabagas". I'm looking for a formula I can use on a separate sheet that will show all of the checked items separated by commas. For example, if someone placed checks next to hats, rutabagas, and gloves, the formula on the next page would display "hats, rutabagas, gloves". Is there such a formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of items being added in a formula | Excel Discussion (Misc queries) | |||
Show Formula / Hide Formula | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
My formula will not show the result, only the formula. | Excel Worksheet Functions | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |