+ Reply to Thread
Results 1 to 4 of 4

How to select Maximum values based on Age in a cell with an existing drop-down list

  1. #1
    Registered User
    Join Date
    02-01-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    44

    How to select Maximum values based on Age in a cell with an existing drop-down list

    Hello all,

    I'm looking to set maximum values of a drop-down list based on the age profile of the relevant person. I wish to keep the drop-down list in place though.

    The drop-down list consists of a list increasing in increments of 0.5% from 1% to 40%.

    I wish to set the maximums as follows:

    Up to age 30: 15%
    Up to age 40: 20%
    Up to age 50: 25%
    Up to age 55: 30%
    Up to age 60: 35%
    Aged 60 and above: 40%

    Is there any way I can enter a formula to allow this?

    Many thanks.

    P.S. I've attached a file for what I mean. Basically I want to restrict the entry value of cell B13 by whatever value is in C5. I would prefer to leave the drop-down list as is regardless, but to restrict the acceptance of certain values being entered based on C5.
    Attached Files Attached Files

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: How to select Maximum values based on Age in a cell with an existing drop-down li

    Do you want the dropdown for say a 25 year old to show 0%, 1%, 2%, ..., 15% and so on?

  3. #3
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: How to select Maximum values based on Age in a cell with an existing drop-down li

    I think what you want is on the attached spreadsheet - sheet2.
    I have defined a range whose length depends on a value derived from a look-up table - input age and get length of list to show.
    The new range, 'Shorter_List' is defined dynamically using the OFFSET() function based on the list of percentages but with its length reduced - see the Name Manager on the Formulas ribbon and Excel help for OFFSET().

    The dynamic range is then used in the combo or Data Validation list.

    The example on sheet 2 shows both a combo and data validation format. You should be able to relicate your preferred method in your front sheet.
    Hope this helps.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-01-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: How to select Maximum values based on Age in a cell with an existing drop-down li

    Wow.......that's great. Thanks a lot for that, really helps a lot, exactly what I need.

+ 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