Disappearing Cell Value

[Steve 1962]

Hi

I have a very annoying issue. I have a cell that is formatted with a FILL that is Purple and the FONT that is white.

Every time I enter the cell and place in EDIT mode by pressing F2, the formula is displayed in the cell however, when I hit ESCAPE to exit the cell, the value is not displayed and the cell appears with just PURPLE FILL but no white font (so output cannot be seen). The formula is obviously still in the cell as it appears in the formula bar.

However, if I exit the cell by pressing ENTER, this does not happen - only when exiting with ESCAPE.

The AUTOMATIC option is selected in the workbook calculation of Excel Options. Macros are disabled and the cells contain no conditional formatting.

Thanks

 

[Artik]

Investigate whether the problem affects only one sheet, all sheets in one workbook, or all workbooks? Because this is not the natural behavior of Excel.

Artik

 

[myall_blues]

Clutching at straws, but are you sure that the option is not set for the sheet to hide zeroes?

 

[RoryA]

What is the actual formula? It sounds like its current value is "" but when you force it to recalculate by pressing enter, it is then producing a different value. It's not obvious from your description how that could be if you are using automatic calculations and no UDFs.

 

[Steve 1962]

Update -

Just to answer some questions -

Once I copy the worksheet to the same workbook, it still does it on the copied worksheet - it is not affecting other workbooks.

The solution that the formula is arriving at is not a zero and the worksheet is set to show zeros.

I have attached an abridged copy of the worksheet here (abridged because the data table in columns A to J is quite large). The problem cell is shown here as M11.

When the entire worksheet is copied to the same workbook, I get the issue. However, it appears that what I have attached here (abridged) works fine.

If the issue was to appear here, all you would see in cell M11 would be purple only.

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	IN	OUT	OUT	Progress OUT	EOY Statement	Fees	Balance1	Balance2	Total			YTD
2	12/12/2022							$1,268,103		$1,268,103			12/12/2022
3	13/12/2022		$931		$931			$1,267,795		$1,267,795			29/12/2022
4	14/12/2022				$931			$1,267,874		$1,267,874			17
5	15/12/2022				$931			$1,272,037		$1,272,037
6	16/12/2022				$931			$1,273,129		$1,273,129		Commence	$1,268,103
7	19/12/2022				$931			$1,263,685		$1,263,685		End	$1,249,532
8	20/12/2022				$931			$1,258,140		$1,258,140		Paid Out	$1,863
9	21/12/2022				$931			$1,254,244		$1,254,244		Growth	-$16,709
10	22/12/2022				$931			$1,247,105		$1,247,105		End ADJ	$1,251,395
11	23/12/2022				$931			$1,254,367		$1,254,367			-1.3%
12	28/12/2022		$931		$1,863			$1,250,902		$1,250,902
13	29/12/2022				$1,863			$1,249,532		$1,249,532
Sheet1 (3)

Cell Formulas
Range	Formula
M2	M2	=A2
M3	M3	=A13
M4	M4	=M3-M2
M6:M7	M6	=VLOOKUP($M2,$A$2:$J$13,10,FALSE)
M8	M8	=SUM(C2:D13)
M9	M9	=(M7-M6)+M8
M10	M10	=M9+M6
M11	M11	=(M10/M6)-1
E3:E13	E3	=C3+D3+E2
J2:J13	J2	=IF(H2="","",H2+I2)

 

[Artik]

I have a suspicion that the sheet may be damaged. If you are copying the entire sheet (from the pop-up menu of the sheet tab, you select “Move or copy...”), then the errors move to the copy of the sheet.
Try building the sheet from scratch, i.e., insert a new clean sheet into the workbook. In the following description, Sheet1 is the original sheet with the problem, Sheet2 is the new sheet. Then:
1. Select the data in Sheet1 (and only the data, not the whole columns) from A1 to Jn, copy and paste into A1 in Sheet2.
2. Select in Sheet1 the range L1:M11 copy, but in Sheet2 paste only the formulas. If there is a need then format the numbers in the range M2:M11.
3. In Sheet2, give fill and font colors in cell M11.

Now check if the problem still occurs.

Artik

 

[Steve 1962]

Thanks Artik

First opportunity I've had to do this. I did it exactly as per your instructions but it still does it.

One thing that I have just noticed is that it is only doing it on cells where I attempt to find the percentage change.

On that worksheet, I have other formulas (and same format = purple fill + white font) that do not exhibit the same problem when I exit using ESCAPE. For example, I have some calculations on that worksheet that are just SUM functions. These work well and I can exit using ESCAPE and all good. However, when I use ESCAPE from cells (M11 & others) that use =(M10/M6)-1, it fails. I have also tried to use another formula to find the percentage change in that cell (M10-M6)/M6, but it still fails.

I know it is a difficult one. Maybe its something that I should report to Microsoft ??? Not sure how to do that.

All of my workbook works fine apart from this issue.

Thanks

 

[RoryA]

Can you censor any confidential data and then post an actual workbook (where it doesn't work properly) to somewhere like OneDrive or Dropbox so that we can have a look at it?

 

[Steve 1962]

Can you censor any confidential data and then post an actual workbook (where it doesn't work properly) to somewhere like OneDrive or Dropbox so that we can have a look at it?

Hi Rory

I'm happy to share the file with you but just not sure how to. Is it something I can email you ?

 

[RoryA]

You need to put it on a sharing site like dropbox or one drive and share a public link here.