Data Validation changes reference, loses list

I've searched for and read other data validation forum posts and answers, but none is quite like my problem. I have a fairly complex workbook with one worksheet serving as the input mechanism and other worksheets with reference data. The input worksheet has approximately 150 data points that must be selected (from drop down lists) or entered by the user. Other sections perform a series of calculations and the result is a formatted output report. All the data validation cells in the input worksheet reference data in other worksheets. Some are cascading lists. The problem occurs sporadically and to different cells with drop down validation lists. The function will work fine and consistently for some time, and then a drop down validation will be blank. When I go to Data Validation I see the range of cells in the reference range has changed to a range of cells in the input worksheet, not the reference cells I had specified in one of the reference data worksheets. I don't know of anything I've done to cause this and as I said it is sporadic. It's annoying and embarrassing as I created this tool for the boss and QA'd it extensively and then when he goes to use it it has these errors. Can anyone provide a solution or tell me where i have erred? Many thanks.

Welcome to the forum

Without seeing how everything is referenced it is difficult to guess what may be the problem.
In my experience data validation lists do not magically change on their own, and so something is making them change.
Are any of the data validation lists refreshed via VBA?
How are Named Ranges created - are they being refreshed via VBA?
Named range "Names" can be set up either at Worksheet level or Woorkbook level - this may be the source of your problem. A worksheet level name can only look to a specific worksheet, but a workbook level name can look to any worksheet for its range.

This may help narrow the focus of your checking
The VBA creates a new sheet, lists all your named ranges and its "refers to" range (in sheet order), with workbook level names listed separately

Please Login or Register  to view this content.

Thanks for your prompt reply and suggestions. I will upload a workbook with the essentials later today. I agree it should not happen and that something I am doing must be causing it. But the really odd piece is that the reference range changes from one worksheet to the input worksheet in all cases that this occurs. Stay tuned for sample. Thxs.

Kev_, Thanks for the suggestion. I'll look into it and see what's going on. Stay tuned.

My bet is on a bit of lazy VBA coding
- using Range("A2") may appear to be ok when a sheet has been selected elsewhere further up the code, but then any workbook level name "RefersTo" range will also look to the SAME worksheet, regardless of which sheet it originally referred to!!

Create a workbook with several sheets, Create a worksheet level named range "Testname" and point it at cell A2 in sheet 1 and then run this code:

Please Login or Register  to view this content.

I am having the same issue, I do not use VBA so I wouldn't expect this to be the cause of the problem. My spreadsheet is locked so no one else has the ability to make changes. I use data validation to create a drop down list using cells on a different sheet in the same workbook. I set the data validation info, save the file, (sometimes) re-open the file and the data validation has changed to the same range of cells but on the sheet that the drop down box is on instead of the sheet where the info was selected.