Existing formula no longer calculates unless I add a trailing space at the end of the formulas.

[ggirf14]

I have a document with about 30 columns calculating based on a single cell value.
This morning the formulas no longer calculates unless I add a trailing space.

The reference cell is set with data validation to select from a list. I found that resetting the reference cell with the same value made all cell calculation happening.


Not understanding is making me nervous this may happen again.


Any lead would be appreciated.

Thanks


Gilles

 

[FDibbins]

This morning the formulas no longer calculates unless I add a trailing space.

Added a trailing space where/to what - the formula or the data?
What is your formula?
Where does your data come from?
what are you searching for?

 

[George J]

It sounds like each formula only worked when you re-actioned it by adding that trailing space.
Is the calculation mode set to automatic or manual? If manual, that may explain the behaviour.

 

[FDibbins]

It sounds like each formula only worked when you re-actioned it by adding that trailing space.
Is the calculation mode set to automatic or manual? If manual, that may explain the behaviour.

A trailing space wont cause a calc to need to be "recalc'd", it will just mean the calc has to be reconstructed

 

[ggirf14]

Calculation were set to automatic all along. To test I changed it to manual and reset to automatic with same result.
I believe FDibbins may be close to what happened, the trailing space (at the end of the formula) was tested on a few cells and all triggered the single cells to work. The problem was on all cells 30 columns wide I did not reconstruct everything.

 

[FDibbins]

If you need to add the space in the formula, that tells me that your data has changed.
Again -
what s your formula and where is your data coming from?

 

[ggirf14]

The data is from a second tab "Input" in the same document.
The formula is in the cell exactly as below (with a carriage return in the formula):
=IF($N$4<>"",
COUNTIFS(Input!E$2:E$3038,$N$4,Input!N$2:N$3038,A18),
COUNTIFS(Input!N$2:N$3038,A18))

 

[MARK858]

with a carriage return in the formula

Why?

 

[ggirf14]

Easier to read for debugging.