Excel Magic Trick #241: Data Validation w Shrinking List




About this tutorial:

Video duration: 9:46
See how to create Data Validation Drop Down Cell List where the List shrinks as you select values. See array formulas, INDEX function formula with criteria, data validation and the OFFSET function used in a named formula that creates a dynamic range of cells.

Incoming search terms:

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

23 thoughts on “Excel Magic Trick #241: Data Validation w Shrinking List

    J Sartrean

    (December 7, 2018 - 6:19 am)

    Is there a way to have only some of the options removed when selected and other always be present in the same drop down menu?

    Vlad the Great

    (December 7, 2018 - 6:19 am)

    Is there a way to select "w4" three times and "w6" two times from the dropdown list?

    decoybja

    (December 7, 2018 - 6:19 am)

    I cannot do this for a list of 1-45. I do not know what I am doing wrong!!!

    Markus Diedericks

    (December 7, 2018 - 6:19 am)

    I have the same question as "me me", how do you create 2 shrinking drop down lists, using the same data? e.g you have 20 teams that have to compete against each other but no team may play 2 times. you'd set it up in a column vs column fashion and I want shrinking dropdowns for that?

    me me

    (December 7, 2018 - 6:19 am)

    i got this to work, but what if i want to do multiple shrinking lists?

    me me

    (December 7, 2018 - 6:19 am)

    i copied what you have for column B and when i go to double click it like you to show the items, nothing shows

    C. McInnis

    (December 7, 2018 - 6:19 am)

    Thank You

    GotWoods?

    (December 7, 2018 - 6:19 am)

    Having trouble editing this. It was working great, but I needed to add to the list and anything I add doesn't work. Any tips?

    The Self Betwixt

    (December 7, 2018 - 6:19 am)

    So I am using this trick, and it almost works perfectly except for 1 minor problem. The list that I am using includes repeating numbers (e.g. 16,17,11,16,8,14). Before I select an item from the drop-down list, 16 is listed twice. As soon as I select 16 one time, the other 16 becomes "#NUM!." Is there any way to have the same value listed twice or more without this error occurring?

    Hugo Pinto

    (December 7, 2018 - 6:19 am)

    Colum C does not show the items as per your instructions. only the first cell is showing the item.

    Hugo Pinto

    (December 7, 2018 - 6:19 am)

    Hi im getting an error message when I click to accept the offset on the validation window.need your help.can't work out where it went wrong.

    QuickMadeUpName

    (December 7, 2018 - 6:19 am)

    I think you can exclude the rows and put just…

    {=IFERROR(INDEX($A$2:$A$15,SMALL(IF($B$2:$B$15="",ROW($B$2:$B$15)-1),ROWS(C$2:C2))),"")}

    Guest

    (December 7, 2018 - 6:19 am)

    Many thanks for sharing your knowledge with us. Is greatly appreciated..
    I have created a "used" list as per your tutorial however am now trying to construct a new one where an additional parameter is true. My items used stretch from C6-Y6. There is a check box below each item where a user selects to make that item available for use elsewhere in the workbook. My formula returns false and looks like this =IF(OR(AND('$C$6:$Y$6=A99,$C$8:$Y$8=TRUE)),1,""). I think its got something to do with the many TRUE conditions the formula encounters with the check boxes as apposed to the unique item names. That portion of the formula evaluates correctly.. Is the AND portion which I cannot fathom.
    Any advice will be very welcome…

    Abhijeet G

    (December 7, 2018 - 6:19 am)

    'VBA method-
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' range C5:C12 will get validation dropdown after macro is run
    ' G5:G12- list of items required in drop down
    'H5:H12 will have formula to determine selection – =IF(COUNTIF($C$5:$C$12,G5),"",G5)  –enter this formula in G5 and drag till G12

    Dim t As Integer, j As Integer, intLastRow As Integer, introw As Integer
    Dim keycells As Range
    Dim txt As String
    Set keycells = Range("C5:C12")

                If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
                
                                   intLastRow = 12
                                   For introw = 5 To intLastRow
                                      If Not IsEmpty(Cells(introw, 8)) Then
                                         txt = txt & Cells(introw, 8) & ","
                                      End If
                                   Next introw
                                
                                txt = Left(txt, Len(txt) – 1)
                                With Range("C5:C12").Validation
                                   .Delete
                                   .Add _
                                      Type:=xlValidateList, _
                                         AlertStyle:=xlValidAlertStop, _
                                         Operator:=xlBetween, _
                                         Formula1:=txt
                                End With
                End If
        End Sub

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    people.highline.edu/mgirvin/excelisfun.htm

    J Saez

    (December 7, 2018 - 6:19 am)

    Great Video! I am trying to apply this to a dependent List but i Believe im Missing something as the "How Many Left" Column isnt working as id like. Any suggestions?

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    You are welcome!

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    You are welcome! Thanks for the 5 star ratings for all the videso that you have been watching!!

    SanadFeisal

    (December 7, 2018 - 6:19 am)

    Thank you sir!

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    It would be a lot to do with formulas, your best bet is to do it with VBA code. I am not good with VBA. Post a question to the Mr Excel Message Board and maybe you can get some help there.

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    Dear soulsaver04,

    Yes, it takes a while to get the hang of array formulas, but once you do you have a new universe of Excel possibilities!

    –excelisfun

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    Dear cbaskis,

    Not that I know. Not with tables set up as databases because the whole column would have formulas.

    However, you could combine it with this video:

    YTLE#75: DV Drop-Down based on DV Drop-Down

    –excelisfun

    ExcelIsFun

    (December 7, 2018 - 6:19 am)

    Dear cbaskis,
    I am glad that the videos are useful!
    I don't know what is causing the problem with your formula without seeing it. Did you download the workbooks I provide? I have a starting workbook with the template that you can fill out and I have a finished workbook with the finished formula. Then you could compare your formula. Not Only That, but the workbooks have lots of notes and reference visoes if you are interested in the topic.
    You could post your formula here also.
    –excelisfun

Leave a Reply

Your email address will not be published. Required fields are marked *