Named cell references don't work in one sheet only

**Ziza** · 05-17-2019, 01:21 PM

In just one sheet in my book, references to named ranges in other sheets do not work, e.g. the value in a range called 'horizon' in Sheet1 can be returned with '=horizon' in Sheet2, Sheet3 and Sheet4 but not in Sheet5.

References in Sheet5 to named ranges within Sheet5 work fine, as do references in other sheets to named ranges in Sheet5, and references in Sheet5 to non-named ranges in other sheets. So it's just some issue with Sheet5 recognising named ranges in other sheets.

So far I've tried:
- closing and opening the document
- renaming the document
- renaming the sheet
- saving in 2004 version
- copying all sheets to a new book
- searching Google and this forum for similar issues

This sheet was created in Google Sheets before being downloaded as an Excel doc and added to this document. I have the same problem if I copy over other sheets from the book created in Google Sheets into my working book, though not if I copy sheets to a new book.

**Pepe Le Mokko** · 05-17-2019, 02:34 PM

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Ziza** · 05-17-2019, 03:52 PM

See attached sheet. It seems that references to new named ranges work, but not references to ranged named before the problem sheet was imported.

**Melvosh** · 05-17-2019, 04:36 PM

If you open the Name Manager (Formulas tab in the top menu), you will see a ton of named ranges with #REF! as the value, as well as #REF! in the refers to. There are duplicate named ranges for specific worksheet and workbook levels. If you delete the worksheet level named ranges that are not working, your workbook level named ranges that are working will automatically be picked up by your formulas. Hope that makes sense.