Results 1 to 6 of 6

Thread: Data validation - using Two word w/ dependent.

  1. #1

    Data validation - using Two word w/ dependent.



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

    Hello - I have created a spreadsheet that has two columns that have a data validation dependent drop down. I need to modify the first column by it being a two word option ( First and Last name) how do I add this without disturbing the dependency of the second column?

    To be more precise- See my attachment:
    This shared spreadsheet is being accessed by Inside Sales reps they are to input appointments they have set for their perspective Regional Managers. In the Source tab I have added the Last name to the "Inside Rep" then if you select the January tab- Column F is the "IS Rep" that will show their name and when that selection is made their Regional MGR's should appear in the Regional MGR's column. I have created a data validation with dependent formula for this by using the ISR Rep's first name. For reporting purposes we now need to have first and last name appear in the ISR Rep column in the data validation. What formula or function do I use and where? I have already added in my source tab the last names. I cant seem to correlate that with their Regional MGR- data validation. Please take a look at my attachment. Where have I gone wrong?! Any help or advice is greatly appreciated!

    Thank you!!
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Hi,

    First you would need to rename the Inside Rep respective named ranges to include the last name, but do not include the space (as it isn't allowed).

    So the name range, Paul becomes PaulNoble.

    Then the data validation formula for column G (Regional Manager) becomes: =INDIRECT(SUBSTITUTE(F2," ",""))


  3. #3
    Ahh thank you for your reply!! Now I updated the name ranges to include the last name with no space and I put that formula in the column G data validation but when i test to chose a selection... nothing appears. I swear I think I'm going crazy! see attached.
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    It looks like the data validation formula in G2 is referencing F3... so it is not coinciding.... please change the data validation formula in G2 to =INDIRECT(SUBSTITUTE(F2," ",""))


  5. #5
    YOU ARE A SAINT! it finally worked! Thank you soooo much! I can't tell you how much I appreciate your help! (:

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    No problem. You are welcome


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
  •