Drop-down list functions.

[RickyDee]

Guys and gals, Good day.
I posted a message a couple of months ago that worked
wonders on my project. I saved the reply and it is safe,
somewhere. But I can't find it. It goes something like
this, and I was hoping someone would remember and fill in
the blanks for me, please --

DROP DOWN LIST
On sheet (2), select A1-A5. In the "name" box type PNAMES.
On same sheet, select A1-B5. In the "name" box type PINFO.
On sheet (1), select A1.
(This is where it gets hairy for me)
Something about VALIDATE, I chose LIST BOX (or some such
thing), press F3 and get the name of whatever it was I
wanted in the first column etc.

A nice person sent this to me and it worked out
GRRREEEAAAAAATTT! And I was keeping it in a safe place.
It's still safe because I can't locate it.

Any Ideas?
Thankees,
RickyDee

 

[Frank Kabel]

Hi
goto 'Data - Validation - List'
Also see:
http://www.contextures.com/xlDataVal01.html

 

[JulieD]

Hi RickyDee

thought the question looked familiar ... i did a google search
(groups.google.com ... advanced search ... just put the group of
microsoft.public.excel* & your name) and came up with the following which i
posted to you on July 3rd ...

A. create your lookup lists
1. type on sheet 1 cells A1:A5 the name of ten people (make them all
different)
2. type on sheet 1 cells B1:B5 a number between 1 & 100 for each person
3. select cells A1:A5 click in the name box (little box above column A to
the left of the formula bar) and type PNAMES and press Enter (no spaces)
4. select cells A1:B5 click in the name box and type PINFO and press
enter

B. create the drop down list
1. click on sheet 2 cell a1 and choose Data / Validation from the menu
2. on the settings tab under the "allow" drop down choose LIST
3. click in the white box under this and press your F3 key - click on
PNAMES in the list and click on the OK button & then on the OK button
again - you will now have a drop down box where you can choose the person's
name

C. now to get the related info
1. click in sheet 2 cell b1 and type
=VLOOKUP(A1,
press your F3 key and choose PINFO and click Ok
then type
,2,FALSE)
press Enter
your formula should look like this
=VLOOKUP(A1,PINFO,2,FALSE)

now if it shows #NA - don't panic
choose a name in cell A1 and the associated value should be displayed in B1
to suppress the #NA when there's no name selected in A1, surround the
VLOOKUP in an IF statement - there's a couple of ways of going but this one
should work for you
=IF(A1="","",VLOOKUP(A1,PINFO,2,FALSE))

*********
then on the 13th i followed a reply post of yours with
*********
to add more items to your list and have them show up automatically in the
drop down & work correctly in the VLOOKUP you need to change your ranges
(PNAME & PINFO) from static to dynamic ... Debra Dalgleish has some great
notes on this on her website at
www.contextures.com/tiptech.html

*********
Hope this helps

Cheers
JulieD

 

[RickyDee]

JulieD
Thank you very much. What you have here is a shortened,
very understandable, version of what is on the web page I
was just sent to. That is certainly helpful, but I am
always in a big push here at work when it comes to my
job. This makes things much easier.

As always,
Thank you very much,and have a nice day.
From Sunny San Diego, it's....
RickyDee

 

[JulieD]

Cheers

JulieD
Thank you very much. What you have here is a shortened,
very understandable, version of what is on the web page I
was just sent to. That is certainly helpful, but I am
always in a big push here at work when it comes to my
job. This makes things much easier.

As always,
Thank you very much,and have a nice day.
From Sunny San Diego, it's....
RickyDee