Issue with Dates() when using formulae with named cell inside the ()|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I have two Excel workbooks that I use to manage my gardening hobby. One book holds all sorts of data and the other has input/output forms to manipulate and display the info in various ways.

The data sheets log all of my purchases, sowing, harvesting, composting records and reminders, and much more.

 My VBA skills are fairly limited but using tips, tricks and snippets from the web manage to finish up with something that works most of the time.

Here’s the crunch – one thing that sort of works but has an irritating interruptions is:

One of the output forms reads data from the jobs to do ‘now’ schedule and outputs a list with the due date  and the task etc.

Some of the dates are frost dependant so I have a named cell ‘Last_Frost’ holding the date of the anticipated last frost in one of the data sheets. One use of this date is to set the job date (relative to frost date) by virtue of a formulae in the due date column of the data file: 

 =DATE(YEAR(Last_Frost),MONTH(Last_Frost)-1,DAY(Last_Frost)-15). Or similar.

While the Sub that produces the output is reading the data file it pauses its progress with a MS Excel window stating:

The name ‘Last_Frost’ already exists. Click Yes to use that version of the name, or click No to rename the version of ‘Last_Frost’ you’re moving or copying. (with buttons Yes, Yes too All, No).  N>B> This interruption occurs TWICE for each time in encounters a date with this type of formulae.

I click Yes and then I get the window:

UpdateValues:FileName.xlsm – where I am offered a list of files to choose from. (With buttons Open Cancel)

Whether I click on the filename where the data is stored and click Open or if I click Cancel the result is the same – the Sub continues and ultimately the final output list is correct. If I click No I am offer a window to input an alternative name.

From the above info can anyone tell me how to overcome this interruption please, while maintaining the reference to the frost date?

I’m sure someone will ask that I attach a copy of the files BUT there are 11 Forms Sheets and  17 Data sheets in two workbooks I would be reluctant to upload the lot and I am not sure I have the skill to chop out the necessary bits and have it work ok to demonstrate the problem!!

Any suggestions please? Thanks, in anticipation.