Forum Discussion
Formula loses link to named column in another sheet
Hi, I have a workbook that consists of two worksheets, Sheet1 & Copy_Paste_List. All the named columns have a scope of the workbook. The named columns show up as you start to write them, while ...
Thank you for taking the time to reply.
I tried the 'Fully Qualified Named Range' option, as that was the only solution I had not yet tried and still the same result. Excel actually replaced 'Sheet1' with the name of the file after I pressed enter.
Each sheet is behaving as if it cannot see the other one. If I change the column name, it accordingly changes it in the formula and yet the link to the column itself is not there.
The only add-in being used (Kofax pdf) I disabled and that did not help either. It is beyond baffling at this point.
I tried the 'Fully Qualified Named Range' option, as that was the only solution I had not yet tried and still the same result. Excel actually replaced 'Sheet1' with the name of the file after I pressed enter.
Each sheet is behaving as if it cannot see the other one. If I change the column name, it accordingly changes it in the formula and yet the link to the column itself is not there.
The only add-in being used (Kofax pdf) I disabled and that did not help either. It is beyond baffling at this point.
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:
- Recreate the Named Ranges: Sometimes, named ranges can become corrupted. You can try recreating the named ranges from scratch. Here's how:
- 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.
- Check for Workbook Corruption: It is possible that your Excel workbook itself is corrupted. To check for this:
- 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.
- Excel Repair: Try running the built-in repair tool in Excel to fix any potential issues with the software itself. Here is how:
- 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.
- 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.
- 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.
- 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.
- Check for Circular References: Ensure that there are no circular references in your formulas. Circular references can sometimes disrupt Excel's behavior.
- Excel Options: Review Excel options, specifically those related to calculations and formulas. Go to "File" > "Options" > "Formulas" and review the settings there.
- I am not the primary owner of this file. I recreated the file by copying and pasting the format followed by a copy and paste of the values only. I decided to go back to the beginning, the very beginning and start writing basic formulas with no error coding etc. When I was double checking the format of the columns, it turns out that despite my having set a column, the SAP column, to a number format, Excel would not keep that format. This was an oddity that I have not come across before either. I had to highlight all the entries and do a correction of converting the text to a number. Once that was corrected the column format of number was maintained and the formula worked.
Thanks again for your assistance.