auto-populate email when name is chosen



I am a complete newbie here - teaching myself excel and all the trimmings.  

I have been working on an "Idiot-Proof" Form for the company I work for and I ran into an issue: when a user selects an employee from the drop-down list in cell F24, I want it to auto-populate the corresponding email address in cell F26.  Currently I have the emails as a drop down list, but the powers-at-be here want it to be automatic.  I've tried the limited knowledge I have with no luck.  I will be tidying this form up as in the Premium Course I purchased.

Any help wouold be greatly appreciated!





Selected Answer

Enter the formula below in F26.


That will give you an immediate result. However, thinking a little into the future, you might anticipate conflict between your dropdown and the formula. Logically, the formula should be over-written whenever the user makes a selection from the dropdown. Then it's gone. Therefore you should probably do away with the validation drop-down and protect the formula instead.

The references to Table5 and Table9 indicate that you didn't name your tables. That isn't good practice. ='Presentation Data'!$U$2:$U$13 to specify the source of email addresses may be idiot-proof in the setup but it isn't that when it coems to maintenance. =INDIRECT("Table5") comes closer but requires the knowledge that the range name must be embedded in an INDIRECT() function here. Idiot-proof maintenance would be assured with =INDIRECT("Emails") but that requires a meaningful name to be given to the table. Of coruse, that same name would also appear in the formula extracting data from the same range.

Note that the formula relies on the sequence of names in tables 5 and 9 being the same. Consider merging the two tables into one with two columns, called, say, Name and Email. In =INDIRECT("Table9") the whole table is specified as a source. While the table only has a single column that is adequate but =INDIRECT("Table9[Email]") would have the same effect, using the column caption to specify it. This addressing syntax would become mandatory if your table has more than one column.



Thank you so much Variatus. I will dive into your suggestions and get this form to work.
metalbender62 (rep: 2) Apr 27, '20 at 10:07 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login