+ Reply to Thread
Results 1 to 5 of 5

Create number list for a second cell based on number selected in first cell

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Create number list for a second cell based on number selected in first cell

    I apologise if this is not a formula/function problem.

    I want users to select a "low" number from a drop down list in G2
    (I have set this up using data validation using a named range that includes the numbers 1 through 12)

    Then I want the user to select a high number from a drop down list in G3

    Is it possible to force the drop down list to only include numbers equal to, or greater, than the number selected in G2 through to a maximum of 12
    ie can I force a way for the choice in G3 to NEVER be less than the choice in G2?

    Example 1:
    In G2 the user chooses "3"
    In G3 the user is presented with a drop down list of only 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

    Example 2:
    In G2 user chooses "8"
    In G3 the user is presented with a drop down list of only 8, 9, 10, 11, 12

    I tried using dependent data validation but because the categories in G1 are numbers, I can't use the name of a range as a number
    Perhaps there is a more elegant solution than my feeble Excel experience can think of?

    Many thanks for any solutions/guidance.
    Attached Files Attached Files
    Last edited by AceCo55; 05-31-2018 at 08:34 AM. Reason: add test file

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Create number list for a second cell based on number selected in first cell

    Try

    =INDEX(Low_Number,MATCH(Sheet1!$G$2,Low_Number,0)):Sheet1!$K$13

    Either as the list source for validation, or to create a new named range which the validation rule can refer to.

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create number list for a second cell based on number selected in first cell

    Thank you for taking the time to answer.

    The formula you provided only returns a single number? (I removed the ":Sheet1!$K$13" as it returned a #VALUE! error ... but maybe I misunderstood)
    I tried using it as the rule in the data validation dialogue box and got this error:
    Snap_2018.05.31_23h45m58s_003_.png


    I need G3 to show a list of numbers that the user can choose from (value in G2 up to and including 12)

    "create a new named range which the validation rule can refer to"
    This is what I'm having trouble doing - I just can't get anything I try to work.

    I have attached a new file with the results I need in the drop down list (G3) for each option in G2
    I'm sure I'm not setting up this table correctly, not naming it correctly and not using the correct syntax/formula in the data validation for G3

    Again thank you for you help.
    Still hoping for a solution.
    Attached Files Attached Files
    Last edited by AceCo55; 05-31-2018 at 10:17 AM. Reason: More information

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Create number list for a second cell based on number selected in first cell

    Not sure why it returned an error for you, although i didn't specifically test it by entering it directly into the validation list box so maybe it doesn't work that way.

    I've added a second named range, 'High_Number' to your original sample and reattached it here, see if this helps.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create number list for a second cell based on number selected in first cell

    Perfect!!!!
    That High_Number Named Range made all the difference.

    Thank you very much for your time and expertise.
    You have solved my problem perfectly!!

    I will sit down and go through
    =INDEX(Low_Number,MATCH(Sheet1!$G$2,Low_Number,0)):Sheet1!$K$13
    so I can learn and understand how it works

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find largest decimal number based on selected whole number
    By primed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2018, 08:22 AM
  2. VBA to Create Dynamic number of Variables based range and each cell value
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2017, 08:44 AM
  3. Replace text selected from drop down list with a number (same cell)
    By Excelbuddy_7 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-06-2015, 11:30 AM
  4. Replies: 12
    Last Post: 01-31-2015, 02:11 AM
  5. Replies: 3
    Last Post: 03-17-2014, 05:48 PM
  6. Random Number From List That Changes for Every Cell Based on Criteria
    By Hellfromabove in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2013, 10:50 PM
  7. [SOLVED] Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer
    By MBeedle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 01:47 PM

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