Drop Down Lists

**robertguy** · 01-08-2012, 05:25 PM

Hi what I am trying to do is to create two drop down lists in columns ‘A’, or ‘B’ which are dependant on each other which will then populate the appropriate rate in column ‘C’.

In Colum ‘A’ I would like to select a cell say ‘A2’ which gives me a list of data e.g.

Principal Project Manager, Senior Project Manager, Project Manager etc etc

If I select any of the choices in A2 the dropdown box in B2 would give me only three choices e.g. Short Term, Medium Term or Long Term. On selection of either Short Term, Medium Term or Long Term their hourly rate would be populated in C2

Date for Column A

Principal Project Manager
Senior Project Manager
Project Manager
Etc
Etc

Date for Column B

Short Term,
Medium Term
Long Term

Data for Colum C i.e. hourly rates

Long Term = £70.00 for Principal Project Manager
Medium Term = £60.00 for Principal Project Manager
Short Term = £50.00 for Principal Project Manager

Long Term = £45.00 for Senior Project Manager
Medium Term = £40.00 for Senior Project Manager
Short Term = £30.00 for Senior Project Manager

Long Term = £25.00 for Project Manager
Medium Term = £20.00 for Project Manager
Short Term = £15.00 for Project Manager

In total I will have 20 various different roles all of which can be Short Term, Medium Term or Long Term all of which have a separate hourly rate for their associated Short Term, Medium Term or Long Term

Any help would be greatly appreciated

Many thanks in advance

Rob

N.B. Excel version 2003

**Richard Buttrey** · 01-08-2012, 06:51 PM

Hi,

Does the attached help?

**robertguy** · 01-09-2012, 06:05 AM

Richard,

Many thanks for your assistance it is very much appreciated.

That said, when I tried to copy your formula:-

=INDEX(tbl_GradeRate,MATCH(A2,LOS,FALSE)+1,MATCH(B2,Data!$A$1:$D$1,FALSE))

into my version of Excel (2003) it does not work. I beleive the problem is 'tbl_GradeRate' as I do not know how to define it in 2003

Could you have a look at the attached file and advise me what I need to do so it works in Excel 2003

In addition is it possible to amend the code so it does not display #N/A if no Grades or Terms selected

Many thanks

Bob

**Richard Buttrey** · 01-09-2012, 06:43 AM

Hello Bob,

See attached...

Ooops, seems the attachment functionality isn't working just at the moment. I'll try later. In case I forget, PM to remind me.

Spoke too soon.. it seems to have attached OK

Regards

**robertguy** · 01-09-2012, 07:15 AM

Richard,

EXCELLENT !! just what I required....but can you explain how you set-up/created/defined the 'tbl_gradeRate' table

Many thanks

Rob

**Richard Buttrey** · 01-09-2012, 07:34 AM

Hi,

It's just a range name that you create with Insert Name Define. The only difference is that I made it a dynamic range name so that as you add new grades the name will expand automatically.

For instance I could simply have defined the name as range A1:D21 on the Data sheet. However if you add a new grade on row 22 the formula will not find it. Hence in these circumstances we use what are referred to as dynamic range names. In this case

Please Login or Register  to view this content.

which uses the Offset() and Counta() functions to define the range limits. So the first bit , OFFSET(Data!$A$1,0,0 says start the range in A1 and offset it by zero columns and zero rows. i.e. it still starts and is locked to A1 since we use the absolute $ character.

Then the second and third elements of the formula, the two COUNTA(() functions are used to define the height and width of the table. So COUNTA(Data!$A:$A) counts the number of non blank cells in column A, hence when you add a new grade in row 22 the counta function will record 22 rather than 21. Similarly the second COUNTA(Data!$1:$1)) function counts the number of non blank cells in row 1, i.e. 4. I could just have used the number 4 but I like to cover all bases in case another column is ever added to the table.

Regards