Dependent Dropdown not updating in subsequent rows

[natoPilot]

Hello, I am very new to excel and VB. I have managed to piece together some functions and code and only have two issues left.

In column C I have a list of employers. In column I there is a dynamic dropdown list of those employers that I can type new data in and it adds it to the dropdown list. In columns D:H there are lists of aircraft tail numbers for each employer. Column J is the dependent dropdown that should allow you to choose a tail number based on which employer you pick in column I. Column I works great and the 1st dropdown in J2 is correct but I can't get the rest of the column J dropdowns to show correctly when I change column I. M2 is the problematic offset function (at least I think this is the problem). I will potentially have 20k rows of data.

Issue 1: I have tried everything I can think of (including adding various defined names to the offset function) to try to get the offset function to work correctly for the whole column but I have hit a wall and need some help with this.

Issue 2: I also need to be able to type new data in the column J dropdown and have it added to the respective list in D:H.

I have attached a sample workbook. Thank you in advance for the help!!

Nate

[Fotis1991]

Hi

Your problem is that the formula in column J(J2), use absolute values​​, and then dragged it so in all cells. If you remove the absolute value, then ok!

Regards

[Marcol]

Have a look at this workbook.

It has what might be a simpler non-volatile solution for your problem.
Add to columns D:H and ths will be reflected in the dropdowns
To increase the table range add a new header to Column I and exactly the same data in C7, then add your sub-data to Column I, don't leave blank columns/rows, repeat as required.

I Have added a test column to illustrate this.

Hope this helps.

[natoPilot]

Marcol,

Thank you for your reply. That is much simpler.

I would still like to be able to dynamically add to the table range and sub-data by typing a new value in either of the two dropdowns. Is this possible? Or is this something that would have to be done in say, a macro?