Rick

Pretty cool. The SUBSTITUTE took out the last comma. Clever. Some comments: The space
after the comma could be included in the formula. If there are no Yes conditions, it
returns an error. It'll get confounded if the city has a comma in it (but that's probably
not going to happen). The OP didn't say if he'd be expanding it for more cities, and it
will require some surgery to accommodate additional cities, and could exceed the allowable
formula length (somewhere around 60 cities if the maximum formula length is still 1024
characters). It has a consistent pattern, so it's doable without too much coffee.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in message
...
This formula will do it, but won't handle the comma and space sequence you want between
the cities. You'll get AtlantaChicagoCleveland.

=IF(B2="Yes",B1,"") & IF(C2="Yes",C1,"") & IF(D2="Yes",D1,"") & IF(E2="Yes",E1,"")

To handle the commas and spaces with a formula might send me into therapy. Someone might
find a manageable way

What about like this...

=SUBSTITUTE(IF(B2="Yes",B1&",","")&IF(C2="Yes",C1& ",","")&IF(D2="Yes",D1&",","")&IF(E2="Yes",E1&",", ""),",","",COUNTIF(B2:E2,"=Yes"))

Rick