View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Tarburton
 
Posts: n/a
Default 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?