Dynamically populate dropdown values based using VLOOKUP and OFFSET?

Hi All,

I am trying to dynamically changing dropdown values in each of the columns of a spreadsheet. I am using OFFSET() to dynamically change the list of values automatically based on the reference list(the reference data is exported from a database on a nightly basis) but I am having trouble getting the dropdown list to filter based on the user entered data. I have attached a spreadsheet example.

Basically I want the user to be able to enter a value in USER ENTERED VALUE(don't worry about validations, I will add those as separately) and have the Dropdown 1, 2, and 3 dropdowns update dynamically. This could be as easy as VLOOKUPS but again, the data could change daily so OFFSET is critical. I have tried OFFSET(VLOOKUP()) and VLOOKUP(OFFSET()) but cant seem to get it working. You can reference the OFFSET() functions I currently have implemented in the Dropdown columns.

Thanks in advance

This can be done by Worksheet event
CODE for the worksheet Product Data

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

So we have five thousand empty rows (10 or so would have been plenty), some empty drop boxes and no expected solutions...

More information and a more proportionately sized worksheet, please.

If I do XXX I expect to see YYY in cells ZZZ, etc.

Sorry about that - the desired outcome is that based on the User Entered Value in column B, columns Dropdown 1, 2, and 3 each will be backed by the proper reference data in a dropdown. For example, if I enter abc123 in User Entered Value, the following dropdowns in each subsequent column will be populated:

Dropdown 1 Values: Data1, Data 2, Data3, Data4
Dropdown 2 Values: 2Data1, 2Data 2, 2Data3, 2Data4
Dropdown 3 Values: 3Data1, 3Data 2, 3Data3, 3Data4

Regarding the 5000 rows, that was taken from a source sheet and does not factor into the solution. Ideally i could apply the solution to the entire column

Modified VBA ..

Please Login or Register  to view this content.

No changed required: see attached.

@JohnTopley
May I know what is the modification in my code.

Hi John,

Thanks - the desired outcome is correct for the example you included but if I enter additional values(such as abc127 in row 5) the values do not filter.

@kvsinivasmurthy: I reversed the insertion of commas and changed the MID to LEFT.

I am sure it will work with MID(DD(T),2) (as it missed off the first letter of the first variable in the drop down).