+ Reply to Thread
Results 1 to 12 of 12

Validation Range keeps excluding some cells

  1. #1
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Validation Range keeps excluding some cells

    Hello Excel gurus! I have a football bowl sheet I've been doing for years but finally ran into a snag. This sheet is meant to score each college football bowl game in points with "confidence points" 1 through 41 assigned to each game. I have a validation range that includes 41 cells (one for each college football bowl game) that are each a dropdown list. However, when I select all 41 cells, it routinely will include all but the last two cells I select and I can't figure out what I have done to cause this to happen. I've tried every solution I can think of and I'm convinced I've done something wrong to cause this. It won't let me edit the validation range and add those last two cells. Been trying everything I can think of for the past two nights.

    The dropdown list in each of the 41 cells references Column C on a hidden sheet that shows numbers 1-41 down the column. The equation used is as follows:
    =OFFSET(Sheet3!$C$1,0,0,COUNT(Sheet3!$C$1:$C$41))

    My Validation Range is simply titled "ValidationRange". The idea is to have each cell show a dropdown of numbers 1-41. Each time a number is selected in a cell, the other cells will not show that number -- when all 41 cells are filled in, each will have a number 1-41.

    Here are the equations used on the 41 lines of my hidden sheet (first being Column A, second is down Column B, and third is down Column C):

    =IF(COUNTIF($B$1:$B$41,ROW())=0,ROW())

    =SMALL(ValidationRange,ROW())

    =SMALL($A$1:$A$41,ROW())

    Any idea what is happening here that is not allowing me to include all 41 cells in my validation range? I'm on Excel 2016. Thanks in advance for taking a peek at this.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Validation Range keeps excluding some cells

    I don't see anything wrong - it works fine for me when I do it.
    There may be something specific to your file, so please attach it, with any confidential data removed. Make sure there is just enough data to demonstrate your need - remove anything which definitely isn't related/needed. 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 - or put some notes on the sheet(s).

    Click on Go Advanced below the reply window, then scroll down to Manage Attachments to open the upload window.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Re: Validation Range keeps excluding some cells

    Thanks for looking at this for me. I've attached a bare-bones version that is small enough to attach here.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Validation Range keeps excluding some cells

    Your Data validation range ("ValidationRange") excludes H107 and X107 and has (it appears) reached the limit of the length of the entry field.

  5. #5
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Re: Validation Range keeps excluding some cells

    Correct. But it worked last year on 42 cells. I can't figure out why it won't work this year for 41.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Validation Range keeps excluding some cells

    As the ValidationRange is incorrect: what has changed as I cannot see how it worked before if the H107/X107 cells were excluded and presumably one other cell to make the 42.

  7. #7
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Re: Validation Range keeps excluding some cells

    Here's last year's sheet that worked with 42 cells in the validation range. Maybe this will help.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Validation Range keeps excluding some cells

    No idea as I cannot even edit the named ranges.

    I did a work around assigning the H/X cells to G1:G41 in the attached and using this range as the "ValidationRange"

    See attached

  9. #9
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Re: Validation Range keeps excluding some cells

    Attachment?

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Validation Range keeps excluding some cells

    ... sorry ...
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-06-2017
    Location
    Omaha, NE
    MS-Off Ver
    2016
    Posts
    6

    Re: Validation Range keeps excluding some cells

    John, you're a genius. It worked! Thank you VERY much for your help.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Validation Range keeps excluding some cells

    Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Need an easy way to count number of cells in a range excluding hidden data
    By jmorris462 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2016, 03:34 PM
  2. [SOLVED] Looking for final four values in range, excluding any blank cells
    By OffTheFairway22 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-02-2015, 07:32 AM
  3. [SOLVED] Avg a range of cells excluding text found in other cells
    By excelneub in forum Excel General
    Replies: 2
    Last Post: 07-17-2014, 07:20 AM
  4. Replies: 0
    Last Post: 06-16-2014, 10:44 AM
  5. Excluding blank cells from a range of data
    By Consty1 in forum Excel General
    Replies: 5
    Last Post: 10-11-2012, 03:19 PM
  6. [SOLVED] Min of range of cells excluding text.
    By basizeland in forum Excel General
    Replies: 4
    Last Post: 07-16-2012, 10:09 AM
  7. Two validation parameters on one range of cells
    By FM1 in forum Excel General
    Replies: 8
    Last Post: 03-09-2010, 08:32 AM

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