[Solved] Name function stopped working

[AllanR]

Thanks in advance if anyone can help. Allan

I do not have much knowledge in excel but several years ago I was given a spreadsheet to keep track of my running. The was a function in this sheet called pace_mile which figured out my pace after I entered my mileage, and time (hours, minutes, seconds) for each run.

This function has worked all the time since 2017 - until 10/6/25. Now when I enter the information no pace is entered into the pace–mile column. If I go back to earlier dates such as 10/5 and change the time, the pace stays the same - no change when I change the time fields. The formula is still listed =pace_mile in the pace column.

What makes this even worse is that when I go to open a previous year's running spreadsheet, I get a message saying there is a problem and do I want excel to try to recover as much as it can. I say yes. Then the previous year opens with the message saying the file could be opened by removing unreadable content. The file is open but all pace values have been removed.

Can someone tell me if this function no longer works and if there is anything I can do? Here is what name manger shows for pace_mile
=IF(ISERROR(IF(dist="","",_xleta.TIME/miles)),"",IF(dist="","",_xleta.TIME/miles))

Also, a repairlog of what I get when I open an old file.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to Running Log 20200.xml</logFileName><summary>Errors were detected in file '/Users/allan/Documents/Excel/running logs/Running Log 2020.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Formula from /xl/worksheets/sheet1.xml part</removedRecord><removedRecord>Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)</removedRecord></removedRecords></recoveryLog>

Attached is a screen shot.

 

[shamimarita]

Hello Allan,

If your Name Manager or named function stopped working, try the following steps:

1. Check for broken references:
   • Go to Formulas ➜ Name Manager and see if any of your defined names show #REF!.
   • If so, edit the reference range and correct it.
2. Ensure workbook calculation is enabled:
   • Go to Formulas ➜ Calculation Options ➜ Automatic.
   • If it’s set to Manual, Excel won’t update named ranges automatically.
3. Verify the scope:
   • Named ranges can be Workbook-scoped or Worksheet-scoped.
   • If your formula references a name defined only in another sheet, Excel won’t recognize it.
4. Check if the function is defined in a custom add-in or VBA:
   • If it’s a UDF (user-defined function), ensure the add-in or VBA module is still loaded and enabled.

If possible, please share a sample of the formula or name you’re using (like =MyName+5), that will help identify whether it’s a range issue, a missing add-in, or a formula syntax problem.

 

[AllanR]

Thank you Shamima. I tried all you suggested without success. But I did have luck. I used a backup from a week ago when everything worked. Then I reentered the data for the past wee. All is fine. I then looked at old spreadsheets and all was fine there too. Something happened, don't know what, but all is fine now. Thanks again.

 

[shamimarita]

Hello AllanR,

You’re very welcome! I’m glad to hear everything is working fine again. It does sound like something in the file may have become corrupted, and restoring from a backup was a smart move.

If it happens again, try saving the workbook as a new file (Save As) — this can sometimes remove hidden corruption. Also, keeping periodic backups like you did is always a great habit!

Thanks for the update, and happy Excelling!