Drop down lists/Named ranges

[creed1101]

Hi

I have a worksheet called Assessments where the user can select an area using a dropdown list. Then In the column 'School' I would like the schools for the area to be displayed. I know I can do this using =INDIRECT($C$2) and use named ranges. However I have another worksheet 'Totals - New Clients' where I would like all of the schools displayed in E8:E19 based on the selection of the area.

It is the second part I cannot do. Previously for both worksheets I would use a columns L,N & P in worksheet 'Assessments'. However that was using nested if statements and I want to increase the number of areas.

Please see attachment and any help is appreciated.

Thanks

Nik

[NBVC]

Try this.

In E21 of Totals-New Clients, change formula to:

=IF(C2="","",COUNTA(INDIRECT($C$2)))

then in E8 use formula:

=IF($C$2="","",IF(ROWS($A$1:$A1)>$E$21,"",INDEX(INDIRECT($C$2),ROWS($A$1:$A1))))

copied down.