Data Validation Combo Box in Excel

[FarzanaSaleem]

I have an Excel File with two sheets. Sheet2 has two columns.
Column A = Position Category
Column B = Position Title

Each Position Category contains some position titles. Some Position Categories have no position titles initially.

In sheet1:
Column A
A1 = Position Category
A2 = C1
A3 = C2
A4 = C3
A5 = C4

Column B
B1 = Position Title C1
B2 = T1C1
B3 = T2C1

Column C
C1 = Position Title C2
C2 = T1C2
C3 = T2C2

I named Column A, B, C as PC, PTC1, PTC2 respectively.

In sheet2:
I used Data->Validation->List.
Source for Column A = PC
Soruce for Column B = =IF(A2="C1",PTC1,IF(A2="C2",PTC2))

Problem is that Column A has four values (C1,C2,C3,C4), whereas column B will either be blank or have two values. When combo box for column B drops, it shows two values and two blank lines.
This is just an example. In actual data, when combo box has scroll bars too, scroll bar stays downward showing blank area and to see actual data, user has to move scroll bar upwards.

One solution that comes to my mind is to name columns A,B,C for specific range, i.e PC refers to Sheet1!A1:A5. But in that case, user will have to adjust the reference each time he enters a new value.

 

[xlbo]

You can use dynamic range names for this - have a look here

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

for more info

Essentially, it would create a range that can expand / contract as data is added / removed

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244