Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dropdown list to return different value


Hello Don/Variatus and all (Variatus will recognise the attachment!)

What I'm trying to do now is make a dropdown list that enters a different value from what is shown.  In this case, I want a dropdown list within the Contract column (C) of the date page that lists the Client Name from the table on the Summary page, but that returns the relevant Contract No in the cell.

I think that the problem with my macro (copied from elsewhere on the web and adapted) is something to do with the naming of the data validation table, or even that this table is on a different sheet, but I'm not sure.

Hopefully this makes sense but no doubt if not then someone will ask.

Many thanks in advance



Selected Answer

Whoo Hoo 

I got it to work

not sure if it is the best way.

just be careful of using a macro to write to a worksheet which has selection change on it.

This dropped into an infinite loop at one point - fortunately I was just stepping through it.




Thanks k1w1sm it works perfect.
To help me in the future, please could you let me know what you had to change to make it work.
Many thanks
JonP (rep: 37) Aug 3, '18 at 12:43 am
Sorry about the lack of explanation. I was trying to get this done in my lunch break and beat Variatus or Don so I can overhaul their points lead. (another 2 hundred years should see it). However, you should be able to see the changes I made to your code. The highlight being resolving how to hand the range to the vlookup. I have used named ranges before but never on a table. In fact, the only times I use tables in worksheets is to help solve problems on excel support sights. Other bits of code, in there, are just around normal macro house keeping with light on dim statements and inclusion of the enablevents true/false. I also changed a bit, that you can't see, that is around the rules for you drop-down list, where I turned off the validation enforcing only values in the list being acceptable.
k1w1sm (rep: 197) Aug 3, '18 at 6:31 am
Add to Discussion

I see that k1 already solved your problem while I still was chewing on it. However, since I got done I will publish my work here. First off, I didn't get your code to work. Full marks to k1 for that!

The approach I took was to create an ActiveX drop-down on the fly when one clicks on an eligible cell. A name can be selected and the associated contract number is written to the underlying cell before the combobox is hidden. Next click it will be deleted and a new one created when needed.

The disadvantage of this system is that the user can't cancel. Once the drop-down shows a name must be selected and a contract number will be written. The way to delete it is to select at least two cells in the same row and clear both of them together.

EDIT 07 Aug 2018   ==================================

It isn't a flaw in Excel, K1. Your data validation list specifies a partial range of the worksheet, not a range in the table. If it would specify the table column as source then it would expand with the table.

Perhaps it is a flaw that you can't use structured references, such as =Table2[Client Name] to specify a validation list but there is a work-around. You can use this type of reference to spacify a named range, and you can specify a named range as source for a validation list.

I have attached a copy of the workbook with your solution to this answer where the idea is put to work. The named range in question is ClientList. You will find the data validation referring to this list, and when you look at the Name Manager you will find the range referring to =Table2[Client Name] . It will shrink and expand with the table.

As we are all investing so much time in this I would like to mention my misgivings about the system. In order to check if the correct contract number was called up one has to repeat the process, meaning the list is error prone from the point of number selection. Therefore it would be better to actually show the name and have the contract number in a hidden column used for counting only. To use less space, the full name could be replaced with a human-readable abbreviation. But that raises the question of why not to use the abbreviation - even the full namein fact - for counting as well. The answer to that is that the same company might have more than one [periodic] contract. As the system stands today no provision seems to have been made for that eventuality however.



Perhaps you could include an empty row in the data range. Unfortunately, I am at home and it is 11 o'clock on Friday night and I can't afford  MS office, at home, so it will be a few days before I get to see if that will do the trick.
k1w1sm (rep: 197) Aug 3, '18 at 6:52 am
You guys are legends and I'd love to be able to undestand vba like this but I've now got totally lost!!!
k1, your code worked great until I wanted to add more rows to the table with contract names and numbers in it and these extra rows didn't show in the dropdown. Good luck in your quest to conquer Don & Variatus!
Variatus, your code works perfect and I have copied it into the template sheet too so that it is there on a new day sheet when automatically produced by clicking the button. I have also deleted the data page as this was only ever introduced to be an end page that didn't change and could be referred to.
Hopefully my workbook is now complete but if not I'll be back on here with another question :)
JonP (rep: 37) Aug 5, '18 at 11:05 am
Hi JonP
Well done you seem to have found a bug in Excel. 
The data validation list does not extend with the table. (I don't use tables but given my limited exposure I would expect more sophisticated behaviour)
It works fine if you insert your new records before the last record or alternatively you can edit the data validation list to extend the range.
k1w1sm (rep: 197) Aug 6, '18 at 9:04 pm
Thank you for this additional detail. As I have previously commented I do not use tables in excel. However, I am slowly acquiring some skills in this area. The original problem revolved around the passing of the range for the Vlookup. My solution probably bypassed sophisticated structures and range names and just blasted in a range. I do use named ranges in data validation lists. My greatest triumph, in this area, is to have a data validation on every city in New Zealand and depending on the chosen city the next validation lists the suburb in that city.
As you have indicated a fair amount of time has been invested here. I have probably got a return on my investment.  
k1w1sm (rep: 197) Aug 7, '18 at 5:15 pm
Add to Discussion

Answer the Question

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