Data validation - using Two word w/ dependent.

alyssaross

New member
Joined
Jan 5, 2015
Messages
8
Reaction score
0
Points
0
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!!
 

Attachments

  • Appointments_Made_by_IS - 2015.xlsx
    274.3 KB · Views: 123
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," ",""))
 
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.
 

Attachments

  • Appointments_Made_by_IS - 2015.xlsx
    273.5 KB · Views: 127
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," ",""))
 
YOU ARE A SAINT! it finally worked! Thank you soooo much! I can't tell you how much I appreciate your help! :)
 
Back
Top