Issue in showing formulas/values

[Dharmendra_Bharwad]

The problem that I face is when I download excel book from Google Drive and enable editing it, it shows #NAME? in all formulas instead of values. Surprisingly, before pressing "ENABLE EDITING" it shows values instead of #NAME?. Both screenshots before enable editing and after enable editing are attached to better understand the issue.

Can anyone guide on this?

[#2]

@Dharmendra_Bharwad 

It would help if you showed us the formulas or attached a copy of the workbook.

[#3]

@Hans Vogelaar 

Sure, it's attached. Basically, the problem is in source sheet "Label" from where the value goes to the earlier screenshots. If F2 & enter is pressed and then only the values shows in these cells. For example, I have done F2 & enter in cell B4, C4, C5. 

[#4]

@Dharmendra_Bharwad 

It's a weird problem, but you can solve it as follows:

• Press Alt+F11 to activate the Visual Basic Editor.
• Press Ctrl+G to activate the Immediate window.
• Type or copy/paste the line shown below into the Immediate window.
• With the insertion point anywhere in that line, press Enter.
• Close the Visual Basic Editor.
• Adjust the column widths.

ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Formula

[#5]

Hi,

It is not solving at my end. Please guide me further.

[#6]

@Dharmendra_Bharwad 

It works for me, so I have no further ideas.

[#7]

@Dharmendra_Bharwad....  After clicking Enable Editing, do the following:

1. Press ctrl+a to select all cells.

2. Press ctrl+h to open the Find and Replace dialog box.

3. Enter IFERROR into the Replace field.

4. First, enter _xlfn.IFNA into the Find field, and click Replace All.

5. Then, enter IFNA into the Find field, and click Replace All.

6. Finally, click Close to close the dialog box.

Explanation....

IFNA is supported in only certain versions of Excel.  See https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461 .

So when I open the Excel file in Excel 2010, I see _xlfn.IFNA to indicate an unsupported function.

(I also see just IFNA in most cells.  They should all be _xlfn.IFNA.  I do not understand why only some references to IFNA are converted.)

Usually, when Excel opens a file, it does not recalculate unless circumstances require it (e.g. "volatile" functions).  So before clicking Enable Editing, you see the last values calculated when the file was saved.

However, when we click Enable Editing, that triggers a recalcuation cycle.  That causes references to _xlfn.IFNA and IFNA to result in #NAME errors.

Replacing references to IFNA with references to IFERROR remedies the problem.

But we must be careful to replace _xlfn.IFNA before we replace IFNA because of the substring similarity.

(FYI, my experience is different.  I see values for cells that reference _xlfn.IFNA both before and after clicking Enable Editing, and I see #NAME for cells that reference IFNA both before and after clicking Enable Editing.  The difference might have to do with the older version of Excel that I use.)

[#8]

@Dharmendra_Bharwad.... You can avoid the "protected view" warning and need to click Enable Editing by changing the file properties in Windows.  In Win7 (my version), I do the following:

1. Save the file to local disk.

2. Right-click the file icon.  Note: The file must not be open at the time.

3. Click Properties, then click Security: Unblock.

4. Optionally, click Apply.

5. Click OK.

Again, in my version of Excel, that does __not__ eliminate the #NAME error.  It just eliminates the Enable Editing prompt.

[#9]

@Dharmendra_Bharwad To add to the collection of answers. I opened your file in Excel MS365. It opens immediately (i.e. without the need to enable editing), showing the NAME errors for all cells except the ones you touched. It looks like this.

All the cells with errors have the @sign preceding the IFNA function. Used Find & Replace to remove all of them. In total, 1361 ampersands where found and removed. 

Then it looks like this:

No more errors.

Attaching your sample file, without errors (for me at least).