Results 1 to 5 of 5

Thread: Combining a formula and a drop down list.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combining a formula and a drop down list.

    Is it possible for me to use a Vlookup in a cell in combination with a drop down list. The purpose would be to allow changing the Vlookup answer by selecting from the list.
    If it is possible to do so, please provide me with instructions.
    I already know how to create vlookup formulas and drop down lists, I just do not know how to combine them, because when I try to add the formula to the cell, I get the message that it does not meet the validation criteria. Thank you for any help.

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Combining a formula and a drop down list.

    Hi Dkehler,

    It's unclear how you want these to work together. Are you trying to change what is looked up dependent of what is selected from the LoV?

    You can do this with Names and INDIRECT. e.g.

    Cell B2 is a Data Validation with a source of =$F$1:$G$1
    F1:G4 are selected and have a Formulas, Create from Selection, Top Row names so I end up with entries in the Name Manager for Dog and Cat, each with their own list.

    In D2 I have another Data Validation LoV with source =INDIRECT($B$2)
    so if you selecet Dog in B2 you get the named range Dog so are offered Rover, Dexter and Max but if you select Cat in B2 then D2 offers Tifer, Fluffy and Tom.

    B C D E F G
    1 Dog or Cat? Select Name Dog Cat
    2 Cat Fluffy Rover Tiger
    3 Dexter Fluffy
    4 Max Tom
    Sheet6


    Workbook Defined Names
    Name Refers To
    Cat =Sheet6!$G$2:$G$4
    Dog =Sheet6!$F$2:$F$4
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining a formula and a drop down list.

    Thank you. That is not exactly what I am wanting to do, but it is an application that I may be able to use on another spreadsheet. Let me try to give a better explanation of what I would like to do:

    I would like for cell H1 to equal D1, BUT if H1 needed to be changed, it could then be done so from a drop down list.

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Combining a formula and a drop down list.

    A cell can be data or a formula (unless you resort to some VBA in the background) so H1 can either be =D1 or a formula; it can't be an LoV.

    To do this the LoV would need to be elsewhere. Here I have it as K1 with selection of K2:K5. If the empty option is selected (or you use the Delete key in K1) then H1=D1, otherwise it takes the K1 value.

    D E F G H I J K
    1 33 33
    2 A
    3 B
    4 C
    5
    Sheet1

    Worksheet Formulas
    Cell Formula
    H1 =IF(K1="",D1,K1)
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining a formula and a drop down list.

    Thank you.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •