excel help... drop down boxes/etc.

[excel_user_1966]

ok, i've got a drop down list box with things in it like Student1, Student2, Student3, etc etc..

When someone selects a name from this list, it puts the name last selected into a cell in excel.

I would like to know how to make it so that rather than write Student1, Student2 etc or whatever is selected into ONE specific cell, i want it to write either Yes or No into the column next to the selected student's name.

thanks for any help with this..

BTW, i've tried doing it with numbers... writing 1,2,3,4 etc. by their names, and then using vlookup etc as someone else suggested, but this isn't possible due to something i can't be bothered to explain now... just blieve me when i tell you i can't do it like that

 

[excel_user_1966]

i hate doing this, i always think it looks awful.... but.... BUMP.

 

[Ian Mac]

but this isn't possible due to something i can't be bothered to explain now...

I can't be bothered to find an answer to somthing as vauge as this, I'd say others may feel the same (not that I can speak for them).

If you explain further I'll give it a go!!!

 

[Ivan F Moala]

On 2002-04-04 04:53, lying_happy_eyes wrote:
ok, i've got a drop down list box with things in it like Student1, Student2, Student3, etc etc..

When someone selects a name from this list, it puts the name last selected into a cell in excel.

I would like to know how to make it so that rather than write Student1, Student2 etc or whatever is selected into ONE specific cell, i want it to write either Yes or No into the column next to the selected student's name.

thanks for any help with this..

BTW, i've tried doing it with numbers... writing 1,2,3,4 etc. by their names, and then using vlookup etc as someone else suggested, but this isn't possible due to something i can't be bothered to explain now... just blieve me when i tell you i can't do it like that

Not 100% sure BUT
Try this;
1) combobox from the forms toolbox called
Dropdown1...you will see the name in the
formula name dropdown list when right clck
ob the shape.
2) Student names A1:A20
3) Name this range Students
4) link the droplist to any cell
(you already have this)
5) Paste this code into a Std module
6) link the shape to this macro

<pre/>
Sub DropDown2_Change()
Dim Ans

Set Ans = ActiveSheet.Shapes("Drop Down 2").ControlFormat

With Range("Students")
If .Item(Range(Ans.LinkedCell).Value).Offset(0, 1) = "YES" Then
.Item(Range(Ans.LinkedCell).Value).Offset(0, 1) = "NO"
Else
.Item(Range(Ans.LinkedCell).Value).Offset(0, 1) = "YES"
End If
End With

</pre>

Now when you select a student it will either
place a YES or NO in...the first time it will
place a YES if empty.

HTH

2)