Named ranges disappearing in XLXS file when emailed

Hi all

I have an issue where an Office 265 XLSX File with a number of named ranges (in Name Manager) is losing all these names when emailed and received by a certain company. Everything else about the file is fine, no issues, however all the named ranges disappear when the file is opened the other end resulting in bad references for data validations.

I have never seen this before and suspect it may be to do with the email virus/spam checking software employed by the company.

Has anyone come across this before? I tried emailing as a zip file however this did not resolve it.

Thanks in advance.

It helped me

Hi, updated to Office 365 - thanks for pointing that out

named ranges are not calculated and are all workbook scope

An example is:

Guidance_likelihood = Guidance!$A$81
RiskMatrix = RiskMatrix!$B$3:$F$7

Originally Posted by AliGW

If this needs to work with Excel 2016, then that should be your profile entry.

There are three named ranges in the workbook - A1 is NOT one of them.

How are these files being E-mailed? What is being used to E-mail them?

I have just E-mailed the workbook to myself and the three named ranges have remained intact. I have a virus checker that checks my E-mails.

There is NO protection in your workbook and no VB - it's an .xlsx file.

I suggest you share an .xlsm file that is showing the problem.

EDIT: I don't have Excel 2016 to test with.

Thanks. I am not sure why everyone is so concerned about Excel Versions in my profile. I deal with many different versions but personally use office 365.

The example sheet is just a cut down version of the actual one with a few named ranges in tact. Yes it’s a xlsx not xlsm that is causing the issue. I may have confused when I said VB protected. It works fine for all
People using any modern version of excel. If it is emailed to a particular organisation (they use 2016 but this is not the issue) and emailed back and I open it in 365 the references are gone. There is nothing special about the workbook. I was just wondering if someone has seen this behaviour before where it seems the file is losing references when being processed through email but it is only one organisation so expect it is email security software doing it.

Originally Posted by Glenn Kennedy

Is the file being sent ONLY to one group of end users (who happen to be using E 2016) or to a range of end users, using a range of Excel products. If the latter, is it ONLY the pre-O365 users who are experiencing problems?

Could any of the VBA code be deleting cell rows/columns (including cells covered by the named ranges)?

Hi. Sorry. It’s just an xlsx with no VB. Nothing special.

It doesn’t matter whether the file is opened in 2016 or 365. Same result. It is happening only after being received by email for a particular organisation

I’m not expecting a resolution here just whether anyone has witnessed this before and may have thoughts

Originally Posted by Glenn Kennedy

Keep your profile as O365!


I've never seen this behaviour before. Same file different organisation = no problem, I assume...

Correct. I expect is is email spam/security filtering that is corrupting the excel. It’s just interesting it’s only the named ranges

Originally Posted by AliGW

You implied it did, but if not, then leave your profile as it is, as Glenn said.

Thanks - was just indicting that the organisation this was emailed use 2016. Apologies for any confusion.

I have seen this exact behaviour. It is extremely unusual and I suspect happens only on networks configured with an extreme level of security. I am trying to figure out exactly what function it is that causes the named ranges to be removed when emailed between domains.