select one option from a box

[gurtej176]

Hi All,

We have a vlookup formula in one cell and the look up value is based on 2 cells , the value of which is based on data validation. Number of items is 15 in one list and 5 in 2nd one. Is there any way to ask user to choose the value in the list without using validation drop down, Like a box with the options listed and then they can choose it by clicking it.

Any idea?

 

[CWatts]

You could use a pair of list boxes. Make a new sheet (or some hidden column on your current worksheet if you want) and put your list of numbers there.

Then create a new form control list box, and draw it on your screen.
Right click it and choose Format Control, like Sheet2!$A$2:$A$16, and for the destination, choose the cell where the selection will be held, say $A$1.

You can then use those cells in your vlookup.

You can use ActiveX controls if you don't want all the extra cells, columns etc, but that will require VBA and it will require all users of your document to enable macros.