Data Validation Question, help needed pleae.

B

BikerGlen

I know the answer is probably somewhere on the Web, but I can't find it. In
an Excel 2002 spreadsheet shown to me, the author had a dropdown Data
Validation list, and a nearby cell contained the position (maybe row
number?) of the selection of an item in that list. In other words, if you
chose the third item in that list, the nearby cell would contain the number
3, if you chose the seventh item on the list, the nearby cell would contain
the number 7, and so forth. I was not allowed to access the spreadsheet as
it was on some one else's machine at another company, so I don't have a clue
as to how this was done. I know virtually nothing about VBA, and need this
ability in a spreadsheet I am trying to write, so it woud be greatly
appreciated if one of you experts could explain how to or even create a VBA
module? for me. I know this is asking a lot, but I havee no choice, I have
been trying to find the solution for over a week and am frustrated to no
end. Many thanks in advance to all who take time to help.

Glen B.
 
J

Jason Morin

This could have been done a dozen ways, but one
possibility is that adjacent to the source list for the
drop-down were corresponding numbers like so:

G H
1 apple 1
2 grape 2
3 raisin 3
4 melon 4

And then he/she used this formula to pull in the row:

=VLOOKUP(A1,G:H,2,0)

where A1 holds the validation.

HTH
Jason
Atlanta, GA
 
M

Max

From what you describe, it could be
a combo box drawn from the Forms toolbar

Try a simple experiment .. ?

In a new sheet
--------------------
Put in A1: Text1
Drag A1 down to A5
(this'll quickly create an input range
for us to fill the combo box)

Click View > Toolbars > Forms
to activate the Forms toolbar

Click on the "Combo Box" icon on the toolbar
and draw it somewhere on the sheet

Right-click on the combo box > Format control

In the Format Object dialog:

Click *inside* the "Input range" box,
then click *outside* the dialog to select A1:A5 on the sheet
(This'll auto-put $A$1:$A$5 inside the box,
which range will populate the droplist)

Click *inside* "Cell link" box,
then click *outside* the dialog and select say, B1
(This'll auto-put $B$1 inside the box)

(You could also have just keyed-in directly
the range/cell references into the boxes, of course <g>)

Click OK
---
Now to test it out ..

Click on the combo box

You'll see a droplist containing the values in A1:A5,

Text1
Text2
Text3
Text4
Text5

Select "Text3" from droplist
The value 3 will appear in the cell link, B2
("Text3" is the 3rd item, in row3 of the input range A1:A5)

Select "Text5"
The value 5 will appear in the cell link, B2

And so on ..
 
M

Max

....
Select "Text3" from droplist
The value 3 will appear in the cell link, B2
("Text3" is the 3rd item, in row3 of the input range A1:A5)

Select "Text5"
The value 5 will appear in the cell link, B2
Click to expand...

Some typo corrections, sorry ..
B2 should read as B1 (the cell link) in the above lines
 
K

Ken G.

Depending on what you want to do, Data Validation from the tool bar may be
more suitable.
Create the list you want to choose from somewhere in the sheet eg Red,
Green, Blue.(can be hidden later if you want).
Click on Data in the Toolbar and Validation then select "List" from the
"Allow" drop down.
Click on the icon at the right hand end of the box that opens and select
your list by dragging over it.
Close this little box, and exit the validation window by clicking OK.
Now when you select the cell with the validation attached to it, the drop
down list will appear by clicking the drop-down arrow, and whatever you
select from the list will then be the value of the cell whether it be numeric
or text. The Combo Box only gives you the position of your selection in the
list, not the selection itself.
 
M

Max

... The Combo Box only gives you the position
of your selection in the list, not the selection itself.
Click to expand...

True, but my reading / response re: the OP's post was that he was
emphasizing more on the *position* functionality (number in a cell link)
viz-a-vis a "droplist" selection, hence thought the fit was probably closer
to a "forms" combo box, which has the functionality he seeks w/o need for
VBA ..
 
M

Max

Received this note from the OP via private email ..
--
From: "GeeBee" <[email protected]>
To: "Max" <[email protected]>
Subject: Re: Data Validation Question, help needed pleae.
Date: Wed, 6 Oct 2004 18:19:39 -0500

Worked perfectly!! Thank you very much, I have been trying to solve this
for over a week, I was obviously looking in the wrong area. Again, many many
thanks for the solution.
 
Top