Data Validation with =&E2 (cell reference)

[lawi227]

I am trying to replicate the following formula in Data Validation:

="Call"&E2
="Email"&E2

I am entering a name into cell E2 - let's say Jane Doe. I want the data validation to read in a LIST: Call Jane Doe, Email Jane Doe

I would typically just type: =Call Jane Doe,Email Jane Doe in the data validation. However the names change, so I need data validation to pull the name from E2.

Can this be done?

 

[rpaulson]

probably need vba or a helper column

 

[davesexcel]

You need the formula in a cell, then use the cell range for the data validation.
For example:
A1 ="Call "&E2
A2 ="Email "&E2

In Data validation.
Select "List" in the allow box
enter in the "Source Box" =$A$1:$A$2

 

[Meat4grinder]

Not sure what your looking for but in E2 you can use the Data Validation list and put in this =OFFSET(Sheet1!$B$1,0,0,MATCH("*",Sheet1!$B:$B,-1),1) and then have a growing list you can choose the name from. Not 100% sure what your looking for.