Data Validation--List references being replaced

Hi--this is my first post. If I've posted on the wrong forum, please accept my apologies.

I'm having a problem with data validation using a list. As follows:

1) General: Excel 2016 and only one computer (so not an issue of different versions/ computers)
2) No VBA or other programming. I'm using the straight Excel options. I'm afraid of VBA.
3) My file is a quite large budget file and requires that the user select categories for each revenue and expense line item entered. It's set up as follows:

(a) Data page, containing all of the lists used in my data validation dropdowns (e.g. expense category/ project/ currency)
(b) Input pages for revenues, special programs, general expenses, etc. All of the input pages pull from the same data page
(c) Each data validation list is used multiple times. For example, on the income page, I've copied the "project" dropdown box (referencing the list of projects), onto all of the rows of the schedule. On the expense and special programs pages, I've copied the "expense category" dropdown box (referencing the list of expense categories) onto the onto all rows of the schedules on all of the pages.

4) What is happening is that the dropdown lists are self-corrupting and are converting from a list referencing the Data page into a list referencing the same cell references but on that specific input page. For example, the data validation list reference will start out =Assumptions!$A$65:$A$86. I close the file. I open it up again and the reference has magically morphed to =$A$65:$A$86, that is, the same cell references, but on the same page.


If someone can tell me how to fix this; I've never had this happen before, I would be very grateful! In addition, I'm going to have to convert this file to Google Sheets (client prefers it ) so any setup suggestions that will help my lists survive the move would be great.

Thank you!

Try making the validation list a named range. Then use the ranged name when setting up the validation dropdown lists. That may solve the problem.

Thanks! I tried that--hopefully the fix will hold.

If it doesn't hold or if you need to add values at the end of that data validation list, you can always convert it to a Table (select, press CTRL+T) and when you add something at the end of the list it will be automatically included in the Table and, thus, also in the data validation list, which will therefore be dynamic.

All you need to do is create the data validation with the list formula like: =INDIRECT("Table3[TITLE]")

Of course you will have to change Table3 to your table name and TITLE to the name of the column in that table.

I actually found that suggestion in another thread and have given it a shot. But I didn't know the CTRL+T shortcut--thank you!

I do love my keyboard shortcuts.