# Formula Questions

#### cll1312

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### nbrcrunch

##### Well-known Member
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.

Replies
7
Views
250
Replies
0
Views
260
Replies
2
Views
398
Replies
1
Views
176
Replies
0
Views
1K

1,195,592
Messages
6,010,621
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back