Dropdown List Issue

[mlo356]

Hi All,

I have a strange issue with a dropdown list. I have 3 dropdown list:

1. Dropdown 1 (B5) is independent (static List)

2. Dropdown 2 (C5) is dependent on dropdown 1 (using a named range to get list based on dropdown 1)

Named Range Formula:

=OFFSET('Drop Down List'!$H$1,MATCH(Tracking!$B$5,'Drop Down List'!$G$1:$G$350,0)-1,0,COUNTIF('Drop Down List'!$G$1:$G$350,Tracking!$B$5))

3. Dropdown 3 (D5) is dependent on dropdown 2 (Using a formula to get list based on dropdown 2)

=OFFSET('Drop Down List'!$I$2,MATCH(1,($C$5='Drop Down List'!$H$1:$H$350)*($B$5='Drop Down List'!$G$1:$G$350),0)-2,0,COUNTIFS('Drop Down List'!$H$1:$H$350,$C$5,'Drop Down List'!$G$1:$G$350,$B$5))

Issue: When I close the workbook and open it, the 3^rd dropdown list (cell D5) is stuck and will not drop down. When I open the validation screen and press “ok” it works again and the list is available. If I press cancel, it doesn’t work.

What I have tried:

1. Adding a piece of VBA code to re-create the dropdown with the formula used to retrieve the list. Basically, simulating me going into the validation screen and pressing “ok”. This did not work
2. Tried using a different cell for the dropdown. This did not work

I do have macros within this workbook. Including a “before close” and an “on open”. The vba within the project file do not affect cell D5 except the password routine which the cell D5 is not set to protect or hide.

I suspect it’s the formula driving the dropdown menu. The reasons I believe this are:

1. When I placed the dropdown and formula for the dropdown into a different cell, I experienced the same issue.
2. When I first created the dropdown, entered the formula, and pressed ok, I got the message stating that it evaluated as an error but I continued any way and the list still worked properly.

I am thinking that maybe on open, it is evaluating as an error and preventing it from working but somehow works again when I go into the validation screen and press ok. This is just a theory though.

Any idea on what this could be?

Any help is appreciated. Thanks in advance.