# Thread: Get data validation list from other sheet of desired choice

1. ## 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'.

2. Try using this Data Validation Formula:

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

3. 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.

4. ## 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

5. ## 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.
Thanks Bob Philips once again

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

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

7. ## problem resolved

Originally Posted by Bob Phillips
Sorry, my bad, you cannot use an array constant in DV.

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

8. I use INDIRECT too

9. Thanks!
Originally Posted by Bob Phillips
Sorry, my bad, you cannot use an array constant in DV.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•