Data validation - using Two word w/ dependent.

**alyssaross** · 2015-01-05, 04:45 PM

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!!

**NBVC** · 2015-01-05, 05:57 PM

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," ",""))

**alyssaross** · 2015-01-05, 06:22 PM

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.

**NBVC** · 2015-01-05, 07:14 PM

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," ",""))