Data Validation

  • Thread starter Thread starter Bernie
  • Start date Start date
B

Bernie

Is there function that returns the position within the
list of the item selected by the user?
So for example if I have a list range of
Apples
Bananas
Oranges

Users selects "Oranges" from the list. Function will
return the number 3. Thanks!
 
-----Original Message-----
Is there function that returns the position within the
list of the item selected by the user?
So for example if I have a list range of
Apples
Bananas
Oranges

Users selects "Oranges" from the list. Function will
return the number 3. Thanks!
.
Let me add that there may be duplicate items in the list
range. So for example, I may have 2 "Oranges" but
depending on which one I select, the function will return
the position of the "Oranges" that I selected.
 
Bernie,

Would it be possible for you to use the drop down from the
forms menu? If you put in a linked cell it will put in the index
of the item picked not the item. Even if you have 2 oranges.

Dan E
 
Dan:

I've been trying unsuccessfully to follow the Excel help
on the "Form..." menu function to create a drop down
list. Can you explain or point me in the right direction
on how to do this? Thanks.

Bernie
-----Original Message-----
Bernie,

Would it be possible for you to use the drop down from the
forms menu? If you put in a linked cell it will put in the index
of the item picked not the item. Even if you have 2 oranges.

Dan E

"Bernie" <[email protected]> wrote in
message news:[email protected]...
 
To use the forms toolbar click
View -> Toolbars -> Forms

Put a Combo Box from the forms menu onto your sheet.
Right click and choose Format Control
Under the Control tab enter the Input range (ie. A1:A10)
it's the range that contains the items you want to appear
in the drop down.
In the Cell Link select the cell which you want the index
number to appear in (ie C1)
Hit OK

Now when you select an item from the combo box the
index # will appear in cell C1.

To view the actual item use
=INDEX(A1:A10,C1)

Dan E
 
Back
Top