Using drop down list to get information about the selected

Re: Using drop down list to get information about the select

What kind of errors are you getting?

texasalynn

Re: Using drop down list to get information about the select

How about VLOOKUP:

Hope that helps,

Smitty

Edit: Apparently everyone posted while I was typing this, but I'm going to post it anyway. It took me long enough

Ok, using your examples above:

In Sheet1, you set the nurses' names as a named range, then set column B on Sheet2 to a data validation list using that named range. I think you've already got that part down, if I remember correctly. While we're at it though, delete the blank columns inbetween the columns of data on Sheet1. It looks nice, but it won't help you in the long run. After that, go ahead and select the entire table and name the range 'data' or whatever you want.

Now we're only going to work with Sheet2. To pull the rates and hours for the selected nurses, you use the VLOOKUP statements.

In E3, enter the following formula:
=IF($B3="","",VLOOKUP($B3,data,2,0))

B3 = the name you want to lookup in the table
data = the named range of the table from Sheet1
2 = the column number from the table you want to return information for the selected name (on Sheet1, Nurses is your first column, Rate your second, and so on).

You can simply copy the formula over to the Hours column, EXCEPT: in the formula, change the 2 to a 3, so it will look up information from the Hours column in the table.
After that, you should be able to simply select those cells and fill the formula down the column; the row references will change where needed.

Or, What this formula says in English:
"If there is no nurse selected in column B for this row, don't show anything in this cell. Otherwise, lookup the name selected in B3 in the table from Sheet1 and put the data from the second column in the table for this record here."

Re: Using drop down list to get information about the select

Thanks for the replies...
I tried that one and I get #N/A for an error.
On sheet2 right below the title "nurse" is a drop down list (B3) with all the names of the nurses from Column A on sheet1.
What I'm trying to learn is to use that drop down list (on sheet2) to pick one of the nurses, then I'll get they're hours and rate to show on sheet2 E3 and G3.

Are you getting the #N/A errors before you select a name from the list? If so, that's because the formula isn't seeing anything in the specified to lookup and can't return a value. So it's showing an error.

Example:

Edit: Formulas located in:

B1: =VLOOKUP($A2,data,2,0)
C1: =VLOOKUP($A2,data,3,0)
B3: =IF($A3="","",VLOOKUP($A3,data,2,0))
D3: =IF($A3="","",VLOOKUP($A3,data,3,0))

Re: Using drop down list to get information about the select

Thank You Very Much Von Pookie...
Thats exactly what I was looking for in ELEMENTARY!!!!!
Thanks a BILLION!!!!!

Glad it made sense! I was a little worried about that

Re: Using drop down list to get information about the select

I highlited the area for the "data" range.... and "defined" that has "data"... but what happens down the road when I add more nurses (I have the "nursename" range long enough not to worry about that range, but what about the "data" range... will I have to keep going back and highlite the whole area again... or did you mean the whole table as in the whole sheet?
and if thats the case... ?????

"Whole table" meant just the data, actually. I'm assuming setting it as a dynamic named range instead would work? I haven't tried that before myself, so I can't give you an example right off the bat.

I know it's been done here on the board quite a bit, though. Maybe a search would turn something up on that?