Combining a formula and a drop down list.

[dkehler]

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.

 

[Toadstool]

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
Dog or Cat?	Select Name	Dog	Cat
Cat	Fluffy	Rover	Tiger
Dexter	Fluffy
Max	Tom

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Cat[/TH]
[TD="align: left"]=Sheet6!$G$2:$G$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Dog[/TH]
[TD="align: left"]=Sheet6!$F$2:$F$4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dkehler]

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.

 

[Toadstool]

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
A
B
C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=IF(K1="",D1,K1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dkehler]

Thank you.