Selection from first Drop Down List determines information in the other cells

Hi, welcome to the forum

i keep finding info about dependant data validation drop down list

That is becasue what you want is not (necessarily/directly) linked to DV. Try this for the Number...
=VLOOKUP($B4,Sheet1!$A$2:$C$7,2,0)
and this for the Phone...
=VLOOKUP($B4,Sheet1!$A$2:$C$7,3,0)

On a side-note...is there a chance that you could have duplicate names?

I can kind of get you started with a formula. You will have to remove the dropdown in E4 and H4 for it to work though. PLace this in E4

Please Login or Register  to view this content.

and this in H4

Please Login or Register  to view this content.

This isn't the final solution, but might help get things going in the right direction.

@ gmr, that is moving in the right direction, and would work just fine for 3-4 names. After that, the nesting becomes more and more complex and involved - and error-prone. A better option, especially with "a long list" (OP's words) would be to use some form of lookup...VLOOKUP() is pretty simple

My bad, I forgot they had a longer list than the one in the sample file.

I would suggestthat you do this the other way round - enter the student ID (which should be unique?), and then pull in the other data based on that?

Thanks guys! yes, the list of students would be long and extensive which rules out the if formula.
Thanks Fdibbins, that works great...can u explain the vlookup value $B4? Why is that the first chosen value? ****My bad - i get it now!!!*** Thanks, last time i used this was at uni
=VLOOKUP($B4,Sheet1!$A$2:$C$7,2,0)
and this for the Phone...
=VLOOKUP($B4,Sheet1!$A$2:$C$7,3,0)

Yes, i should put student number first.....

Can you upload a revised WB, with a sample output plz? Im not sure I understand the DD part? Do you just want a list of all A's? If so, have you considered looking at using filters on sheet1? If you just want a seperate table showing all A's (or whatever you select), we can, of course this with formulas...
Your table...

	A	B	C	D
1	Names	Student Number	Phone Number	Present
2	Hank	1111111111	1111111111	a
3	Tammy	2222222222	98765432	p
4	Len	33333333333	9876543	p
5	Amber	4444444444	23456789	a
6	Tony	5555555555	34567890	p
7	Bob	666666666	45678912	a

Extract table...

	G	H	I	J	K
1	Names	Student Number	Phone Number	Present	A
2	Hank	1111111111	1111111111	a
3	Amber	4444444444	23456789	a
4	Bob	666666666	45678912	a

K1=selection cell/drop-down
G2=IFERROR(INDEX(A$2:A$7,SMALL(IF($D$2:$D$7=$K$1,ROW($A$2:$A$7)-1),ROWS($A$1:A1))),"")
This is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

copied down and across

Hi, yes. Basically i made an absence column. A is for absent and / is present. I put filters so the only students i can see are those with the A. How can i make only those with the A appear on the drop down on the following page? I want to elimate such an extensive list appearing on the next page (sheet2).

Also Sheet2 is a sort of contact log, students must be contacted when they are absent. Is there a way to keep the sheets so that i can add on reasons for absence,so they can be absent on many different days, but how can i keep these sheets. It would be good if for every absent student it generated a sheet which is kept on a seperate tab.

So, if i filter all the absent and present students and 2 are absent, then the sheet that appear on sheet2 is created twice ...i hope this makes sense, or maybe someone can suggest a better idea for this

That array formula has me confused

Not sure where you want to put this, but copythis ARRAY formula down and acroos...
=IFERROR(INDEX(Sheet1!A$2:A$7,SMALL(IF(Sheet1!$D$2:$D$7="A",ROW(Sheet1!$A$2:$A$7)-1),ROWS(Sheet1!$A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Did you use CSE to enter?

	A
4	Names
5	Tammy
6	Len
7

BTW any ideas on being able to generate and keep sheets for each absent person so i can go back and add more contact info at later dates?

If you are keeping a running data base, that should not be a problem

Yes the array worked...the curly brackets appeared...but the only name that appeared was Tammy, maybe its just me

Can you elaborate on how to generate and keep sheets for each absent person?

The array is still returning one result, not all the people who are absent just one....weird