Formula Questions

[cll1312]

Hi,

My name is Chana and I am new to the site and excel so I hope that I am posting correctly. Here is my problem, I am trying to create a formula that will return a value (the name of a summer abroad program) if there is a number in the coloum of the record indicating it. Here is the formula I tried:

=IF(ISNUMBER(AF:AF),"Berlin",IF(ISNUMBER(AE:AE),"Amsterdam",IF(ISNUMBER(AG:AG),"Cambridge",IF(ISNUMBER(AH:AH),"London",IF(ISNUMBER(AI:AI),"Paris I",IF(ISNUMBER(AJ:AJ),"Paris II",IF(ISNUMBER(AK:AK),"Rhodos I",IF(ISNUMBER(AL:AL),"Spetses",IF(ISNUMBER(AM:AM),"Rhodos II",IF(ISNUMBER(AN:AN),"Siena"))))))))))

This formula works, but it does not do exactly what I want. It will only return one value. I would like it to return as many values as have a number in the coloumn. Like if a student signs up for more than one program, it would show up with all of them.

The pertinent part of the table looks like this:
<TABLE style="WIDTH: 717pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=953 border=0><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2432" width=76><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1568" width=49><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2880" width=90><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2528" width=79><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1920" width=60><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4160" width=130><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5056" width=158><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 57pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=76 height=21>(column E)
Code
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=81>(column AE)
AMSTERDAM SESSION 1 (July 2 - July 18, 2009) AMSTERDAM SESSION 2 (July 20 - August 1, 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72>(column AF)
BERLIN SESSION: (July 26 - Aug. 8_ 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=49>(column AG)CAMBRIDGE SESSION: (July 6 - July 18_ 2009)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=90>(column AH)
PARIS SESSION 1: (July 1 - July 14_ 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79>(column AI)
PARIS SESSION 2: (July 19 - July 31_ 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 45pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=60>(column AJ)
LONDON SESSION: (July 20- August 4_ 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 98pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=130>(column AK)
RHODOS SESSION 1: (May 31 - June 19_ 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=158>(column AL)
SPETSES SESSION: (June 21 - July 10, 2009)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>(column AM)
RHODOS SESSION 2: (July 13 - August 1_ 2008)
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72>(column AN)
SIENA SESSION: (June 7 - 26, 2009)
</TD></TR></TBODY></TABLE>

Column E is where the formula will be used, and the data will come from Columns AE to AN. If the student selects amsterdam there is a 1 in cell AE of their record, and so on.

Thanks in advance for your help.

 

[nbrcrunch]

First you need to rearrange your data. For your criteria, Excel deals with columns easier than it deals with rows.

So,
In column A, row 1, put the header: "Locations"
Under that put all your locations

in column B, row 1, put the header: "Select"
Under that place a 1 next to each item

Turn on filtering and base it on column B where value is 1
Without any formulas, you have the list you want

You do not need to retype your location. Merely select all those cells and copy them
next, select cell A2, right-click and choose "Paste-Special."
From the Paste-Special menu, choose Transpose and click ok.