New field from Drop-Down 1

[TonyU]

Hello list.
I have a drop-down box on an Excel sheet and I would like that when the user makes a paricular selection a field appears next to the drop-down box for them to add additional comments. Would this be an easy thing to do or am I crazy ;-). Thanks in advance. Tony

 

[Guest_imported]

There should be no problem doing what you want. If you explain with an example I could help you.

questions:
is the box a combo box?
what do you means by field appears? do you want the comment to be associated with the value in the combo box?

 

[TonyU]

Thanks for responding Dark_sun.
Yes it is a combo box. This is what I'm doing.

I have 20 people on a combo box and I would like that when one is selected he's or her's email address appears on a separate field within that sheet.
I tried using nested "IF" statements but I could only use 8 at a time. Does this make sense? Thanks in advance. Tony

 

[Guest_imported]

where is the list of Names / email addresses stored?

 

[TonyU]

They are on a hidden cell within the book Tony

 

[Guest_imported]

Okay, all you need to do is:

Make sure the combo box has a linked Cell, you can set this by right clicking on the combo box when in edit mode and choosing format control. it doesn't really matter what cell you choose. lets say A1.

once this is done you can put a formula in the cell which you would like to display the email address:

formula: =Vlookup(A1,Sheet2!A1:B10,2,False)

A1 is the linked cell
Sheet2!A1:B10 is the range where the list is stored
2 is the column data to return
False is the match type.

If you need anymore help let me know.

 

[TonyU]

Here's what I'm getting Dark_sun
(I really appreciate your help BTW)

Here's my format control:
Input Range: T4:T20 ----> These are users names (hidden)
Cell Link: =Vlookup(P4,Sheet1!T4:T20,2,false)

and I get this error:
Reference is not valid

X-) Tony

 

[DarkSun]

Sorry, my message wasn't clear enough.

Cell Link: A1


Then enter formula into the cell where you want email address to appear.

fomula: =Vlookup(A1,Sheet1!T4:U20,2,false)

notice range of Sheet1!T4U20 range must contain name and email address columns. (2 columns)

 

[TonyU]

Sorry to keep bugging you with this Darksun. but now I'm getting this error on the cell where I want the email to show

error = #N/A
and when I want to edit the cell, a colored rectangle appears around my linked cell (A1) and my names and emails (T4:U20)) Tony

 

[DarkSun]

#N/A means it can't find the value in the range.

Check that the value in (A1) appears in the Range (T4:T20).


If you want you can email me the file and I will quiclky set it up for you. Or I can email you an example.

my email address is dark_suns@lycos.com

 

[DarkSun]

Sorry, My fault. I'm used to using VB Combo boxes.

In the cell where you want the address put the formula.

formula : =INDEX(U4:U20,A1)

Sorry about that. the combo box returns the index of the chosen value. not the value itself.

hope this works.

 

[TonyU]

Did you get my email? Tony

 

[DarkSun]

have you tried my last post?

 

[TonyU]

Yes Sir, and I get #REF! Tony

 

[DarkSun]

I have emailed you the file.

 

[TonyU]

Thank you very much for you time and assistance. You got my vote my friend. Tony

 

[DarkSun]

Thank you. Do you need the text to be a link?

 

[TonyU]

Yes Tony

 

[DarkSun]

That is a bit harder.
I will write it tonight for you.
I will have to use VB unless i can find another way.

I will email you it tomorrow if that's okay?

 

[TonyU]

Tomorrow is fine. (don't go out of your way tho, if you can, fine. If not, fine also. Thanks Tony