Get data validation list from other sheet of desired choice

hello everybody,
I want to get a data validation list in dress form sheet in cell J1 after choosing a fixed condition in cell H1. I have created a formoula in sheet2 but it dosent work as I paste it in data validation options list of refer to box. pls help me to get the right formula which I can get to right list. for ur convenience I have also created the named ranges. I am a primary teacher and wants to make an excel sheet that every school can use it as most of school teachers dose'nt know enough about excel formulas. I have attached the sample file named "ALL STUDENTS LIST DESU 2015-16'.
THANKS IN ADVANCE

Try using this Data Validation Formula:

=INDIRECT("_"&LEFT(H1)&RIGHT(H1))

I think it needs to be a bit smarter than that to cater for 1ST, 2ND, 3RD, 4TH etc. with no letter

=INDIRECT("_"&LEFT('DRESS FORM'!H1)&IF(OR(RIGHT('DRESS FORM'!$H$1,2)={"ST","ND","RD","TH"}),"A",RIGHT('DRESS FORM'!H1)))

BTW, your DV list has IST C, not 1ST C.

error

Hi friends
Thanks for ur replies. As I paste the the second last formula of reply then it shows error that it cant accept union intersection etc. in refer to box of data validation list

error

Thanks for ur replies. As I paste the formula then it shows error that it cant accept union intersection etc. in refer to box of data validation list.
And thanks for finding my mistake in dv list of H1 cell.
Waiting for ur reply.
Thanks Bob Philips once again

Sorry, my bad, you cannot use an array constant in DV.

Try this instead

=INDIRECT("_"&LEFT('DRESS FORM'!H1)&IF(MID('DRESS FORM'!$H$1,LEN('DRESS FORM'!$H$1)-1,1)<>" ","A",RIGHT('DRESS FORM'!H1)))

problem resolved

Bob Phillips said:

Sorry, my bad, you cannot use an array constant in DV.

Try this instead

=INDIRECT("_"&LEFT('DRESS FORM'!H1)&IF(MID('DRESS FORM'!$H$1,LEN('DRESS FORM'!$H$1)-1,1)<>" ","A",RIGHT('DRESS FORM'!H1)))

Click to expand...

thanks Bob
I am very thankful to u. All of my staff and students are giving u wishes that u will achieve everything in ur life what u want.
thanks for helping us.
neeraj arora

I use INDIRECT too

Thanks!

Bob Phillips said:

Sorry, my bad, you cannot use an array constant in DV.

Try this instead

=INDIRECT("_"&LEFT('DRESS FORM'!H1)&IF(MID('DRESS FORM'!$H$1,LEN('DRESS FORM'!$H$1)-1,1)<>" ","A",RIGHT('DRESS FORM'!H1)))

Click to expand...