I have an excel sheet that is saved in .xlsx format. on sheet 1 has 4 pivot tables that update with data dynamically. i've named those columns and used data validation named range in dependent data validation drop downs on sheet 2 ( 4 of them) in a table. The drop downs work correctly up until the point when i reopen the book. the two drop downs to the right of the table still show but my lists do not pop-up for those. when i go to the data validation options for that cell and not change the formula but just press "ok" , the drop down works again.
Solved by M. E. in 29 mins