Results 1 to 9 of 9

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

  1. #1
    Seeker neerajarora8077's Avatar
    Join Date
    Apr 2015
    Location
    delhi, india
    Posts
    7
    Articles
    0

    Red face Get data validation list from other sheet of desired choice



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try using this Data Validation Formula:

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


  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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. #4
    Seeker neerajarora8077's Avatar
    Join Date
    Apr 2015
    Location
    delhi, india
    Posts
    7
    Articles
    0

    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. #5
    Seeker neerajarora8077's Avatar
    Join Date
    Apr 2015
    Location
    delhi, india
    Posts
    7
    Articles
    0

    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
    Last edited by neerajarora8077; 2015-04-08 at 03:14 AM. Reason: name

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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)))

  7. #7
    Seeker neerajarora8077's Avatar
    Join Date
    Apr 2015
    Location
    delhi, india
    Posts
    7
    Articles
    0

    problem resolved

    Quote Originally Posted by Bob Phillips View Post
    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)))
    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. #8
    I use INDIRECT too

  9. #9
    Thanks!
    Quote Originally Posted by Bob Phillips View Post
    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)))

Tags for this Thread

Posting Permissions

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