Formula loses link to named column in another sheet

OMGHelp 

It is indeed perplexing that you are still experiencing issues even after trying the suggested solutions. To further troubleshoot the problem with named ranges losing their link when used in formulas across different sheets in Excel 2016, here are some additional steps you can take:

1. Recreate the Named Ranges: Sometimes, named ranges can become corrupted. You can try recreating the named ranges from scratch. Here's how:

1. Go to the "Formulas" tab. b. Click on "Name Manager." c. Delete the existing named ranges that are causing issues. d. Recreate the named ranges with the correct scope (workbook scope) and ensure they are correctly assigned to the columns in the respective sheets.

1. Check for Workbook Corruption: It is possible that your Excel workbook itself is corrupted. To check for this:

1. Create a copy of the workbook for backup purposes. b. Open the copied workbook and see if the issue persists. If it does not, it might have been a corruption issue in the original file.

1. Excel Repair: Try running the built-in repair tool in Excel to fix any potential issues with the software itself. Here is how:

1. Close all Excel workbooks. b. Go to "Control Panel" > "Programs and Features" (or "Add or Remove Programs" depending on your Windows version). c. Locate Microsoft Office in the list of installed programs, right-click it, and choose "Change." d. Select "Repair" and follow the on-screen instructions to repair Microsoft Office.

1. Use a Different Workbook: Create a new, blank workbook and try reproducing the issue with simple named ranges and formulas. This can help determine if the problem is specific to your existing workbook or a more general Excel issue.
2. Excel Updates: Ensure that your Microsoft Excel 2016 installation is up to date with the latest updates and patches. Sometimes, updating Excel can resolve unexpected issues.
3. Excel in Safe Mode: Launch Excel in Safe Mode to see if any third-party software or add-ins are causing the problem. To do this, press the Windows key + R, type "excel /safe" (without quotes), and press Enter.
4. Check for Circular References: Ensure that there are no circular references in your formulas. Circular references can sometimes disrupt Excel's behavior.
5. Excel Options: Review Excel options, specifically those related to calculations and formulas. Go to "File" > "Options" > "Formulas" and review the settings there.