Nested IF Function not working!! HELP!!

[Alice21]

Hi

In short - I have a long nested IF function that works well for a few rows then does not display the '0' text I would expect when the statement is false.

Basically I have 12 geographical areas and in these areas are a number of teams. I store the list of areas/teams in a tab called Lookup

Example geographical area 1 = Wales
Teams = Caerphilly, Breacon, Cardiff, Newport

Example geographical area 2 = England
Teams = London, Manchester, Kent, Surrey, Newcastle, Birmingham, Liverpool

What I have on my front sheet in cell A56 is a drop down of the geographical areas for uses to select (i.e Wales).
In cell A57 I want the formula to automatically bring me the first team name in that selected area (i.e Caerphilly). So basically the formula checks the area name in A56 and if it's a match display the first team name. This works fine but when I drag the formula down to show the second team name, third name etc..once there are no more Wales teams it starts showing the England teams even though Wales is the area selected A56. I would expect the cells to show a 0 after the last Wales team

I have a huge data set and I am unable to quickly draw up a blank spreadsheet (sorry) for but this is what I have at present:

Please Login or Register  to view this content.

Any ideas why the formula does not show a 0 when false? OR, can anyone think of different way of doing this?

I just need the users to select an area from a drop down and the teams below to update automatically to show the relevant teams for that area.

Sorry if I am not very clear, I have to get this off today as I am on leave until July!

[BuntyMac]

Hi there,

I am in the process of completing something similar and have found the solution in dependant drop down lists. I found this site very useful.

http://www.contextures.com/xlDataVal02.html

Basically you would set up a List of Geographical areas.
on the next column you would put Wales with the teams below: Caerphilly, Breacon, Cardiff, Newport
On the next column England with the teams below: London, Manchester, Kent, Surrey, Newcastle, Birmingham, Liverpool

https://youtu.be/C2JOhit9hEI

[Alice21]

Hi there

Thank you for that, i'll take a look

In my mad desperation I think I may have found something that could work also, yet to fully try it...

http://www.get-digital-help.com/2011...ltiple-values/