Using drop down list to get information about the selected

[Powerdog]

I'm trying to learn how to use a drop down list (B3 on sheet2) to get that person's information (hours and rate) from (E3 and G3) sheet1 to show up at E3 and G3 on sheet2 with that person's name in the list box.
Column A on sheet1 is "Defined" as nursesname.
I got some great help yesterday... but I still can't get a grip on how it's done. I keep getting errors...
I created this smaller example so I could understand easier how to link it all together.
I really do appreciate the patience and help.

Book1.xls
	A	B	C	D	E
1	Nurses		Rate		Hours
2
3	Barbara		10		33
4	Dawn		15		42
5	James		32		15
6	Shelly		9		51
Sheet1

Book1.xls
	A	B	C	D	E	F	G
1
2		Nurse			Rate		Hours
3
Sheet2

 

[Smitty]

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

Aladin and Yogi chime in on Dynamic Named Ranges here.

Smitty

 

[Von Pookie]

GO ME! *ahem* I mean, I got it (hey, I've never tried this before, I'm happy it worked )

This worked for me:
Go to Insert\Name\Define and select the 'data' range from the list.

In the 'Refers to:' section, enter this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

This should set the range as A1 being the top left cell of the range, and then it counts down column A to get the number of rows used, and counts across row 1 to get the number of columns.

You won't be able to select the range from the name box as with a normal named range, but you can test if it worked by pressing Ctrl+G and entering 'data' in the reference box and pressing enter. It should select the range. And if you add anything to the table, it should catch those as well.

You could also do this for the Nurses range if you wanted, instead of the entire column or whatever you did. For that, you would use:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

This should then also work with your VLOOKUP formulas on Sheet2 and the named range you use for data validation without having to worry about new information to the list being included