+ Reply to Thread
Results 1 to 7 of 7

Drop Down Lists

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    Posts
    465

    Question Drop Down Lists

    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
    Last edited by robertguy; 01-11-2012 at 08:35 AM. Reason: To mark as solved
    Rob

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,132

    Re: Drop Down Lists

    Hi,

    Does the attached help?
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    Posts
    465

    Question Re: Drop Down Lists

    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
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,132

    Re: Drop Down Lists

    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
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    Posts
    465

    Question Re: Drop Down Lists

    Richard,

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

    Many thanks


    Rob

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,132

    Re: Drop Down Lists

    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

  7. #7
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    Posts
    465

    Re: Drop Down Lists

    Richard

    Many thanks for your solution and explanation it is just what I wanted and works a treat.

    Thanks again

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1