+ Reply to Thread
Results 1 to 6 of 6

Drop down list of numerical range instead of every whole number

  1. #1
    Registered User
    Join Date
    11-09-2019
    Location
    Utrecht, Netherlands
    MS-Off Ver
    2018
    Posts
    4

    Drop down list of numerical range instead of every whole number

    Hi everyone,

    I have a ranking list of 500 or so names, all listed down from 1 up to and including 500. It's quite a long list, so I'm trying to cut it down in intervals of 50 so it's easier to read.

    Is there a way I can have the drop-down to show (1-50, 51-100, 101-150, etc.) instead of (1, 2, 3, 4, 5, 6, 7, etc.)? That way, I can keep the list to 50 rows instead of 500.


    Thanks!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Drop down list of numerical range instead of every whole number

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor shank_mis's Avatar
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    128

    Re: Drop down list of numerical range instead of every whole number

    Hi llaand,

    Are you looking for something like this ?
    Please see the attached file.
    Attached Files Attached Files
    Shashank Mishra
    Please hit "Add Reputation" Button if you liked the answer.

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Drop down list of numerical range instead of every whole number

    you need as minimum a selection of the offset, additionally a selection of # to show
    data validation formular can be
    Please Login or Register  to view this content.
    (see attachment)
    where $A$1 is the header of your list you want to show
    $F$S is the offset of your list (beginning with 1)
    $G$2 is the number of values to show in the dropdown

    cheers,
    Guenter
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-09-2019
    Location
    Utrecht, Netherlands
    MS-Off Ver
    2018
    Posts
    4
    Quote Originally Posted by shank_mis View Post
    Hi llaand,

    Are you looking for something like this ?
    Please see the attached file.

    Wow yeah this is exactly what I mean! Thank you! But when I choose a different option in the drop-down list, I don't see the numbers. Is there a fix for this? I've tried to play around with it but I'm afraid I simply don't know how.

  6. #6
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Drop down list of numerical range instead of every whole number

    I utilized your spreadsheet and made a few tweaks. changed your ranges a little bit.
    Attached Files Attached Files
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

+ 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. assigning numerical values to a drop down list
    By ashtreyy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2016, 11:34 AM
  2. Replies: 3
    Last Post: 10-08-2014, 04:17 PM
  3. [SOLVED] Need a funtion that will retern a numerical value based on a drop down list.
    By jnwc82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-03-2014, 10:45 AM
  4. Replies: 13
    Last Post: 07-29-2014, 10:57 AM
  5. [SOLVED] Range in one drop down list depended on the selection in another drop down list
    By masben in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2013, 10:36 AM
  6. Replies: 3
    Last Post: 04-10-2011, 03:54 PM
  7. Replies: 2
    Last Post: 08-17-2005, 04:05 PM

Tags for this Thread

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