Dynamic Name Ranges & Drop-Down Lists (Difficult) HELP!

**STBTC** · 12-01-2016, 06:13 PM

I have sheet one with column A where names will be entered. Once names are entered, column B with populate the initials. These two columns are dynamic ranges. Sheet two, column A will have a drop-down where the selections would be the names, BUT when selected the initials that correspond to it would populate the cell instead.
Help.
Also, if names are deleted, modified, or moved, the drop-down list wouldn’t show blanks. All formulae are NOT to be in cells, but as dynamic ranges or otherwise.
I will PAY to learn, if need be. Ha ha
Is it possible?
HELP!!!!

**Hercules1946** · 12-01-2016, 06:29 PM

I think it would help to see some sample data illustrating your expected results. Also check your private messages.

**STBTC** · 12-01-2016, 07:03 PM

I made an example, but the ExcelForum website won’t allow me to attach anything. Every other button I click is fine, just the attachments button won’t do anything.

**STBTC** · 12-01-2016, 07:11 PM

Also, as far as initials being returned when names are entered, I found this function online: =if(len(B3)-len(SUBSTITUTE(B3," ",""))=0,left(B3,1),if(len(B3)-len(SUBSTITUTE(B3," ",""))=1,left(B3,1)&mid(B3,find(" ",B3)+1,1),left(B3,1)&mid(B3,find(" ",B3)+1,1)&mid(B3,find(" ",B3,find(" ",B3)+1)+1,1))

I don’t want it to be in the cells, I would prefer it to be a part of a dynamic range; but every time I try to apply it, something happens that results in an error. I conjecture hat it wouldn’t work, or I’m missing something.

**Tony Valko** · 12-01-2016, 07:11 PM

To attach a file to your post...

Click the Reply button to open the Reply Editor
Click the Go Advanced button
Scroll down until you see the Manage Attachments link and click that
Click the Browse button and select your file
Click the Open button
Click the Upload button
Click the Close Window button
Click the Submit Reply button

What you want to do will require a VBA procedure.

There is a sample file that demonstrates this here:

http://www.contextures.com/excelfiles.html#DataVal

Scroll down and look for: DV0004 - Data Validation Change

**STBTC** · 12-01-2016, 07:17 PM

This is an example I threw together in its most basic form.

**dflak** · 12-02-2016, 04:04 PM

Since you are trying to put both a formula and a value in the same cell, you cannot use a formula for this. You must use VBA. The following workbook has a Change event fired off column A that looks up the initials associated with the name and replaces the name with the initials.