formulas are not automatically updating

[bonusdias]

I am using office 365 and excel 2016. I've created some CSE formulas that are working exactly as I intend them to and giving me the correct values. My issue is that not all the formulas are updating automatically.


So I enter in data in one worksheet and the CSE formulas all populate in that worksheet. I go to the other worksheet and those CSE Formulas are all populated then I go to the last worksheet and there are no values for the CSE formulas.
Shift F9 doesn't work. All that works is copying down the formula again.

When I copy the formula down again all the values show up, so then I go back to the original sheet and add another piece of data, all the CSE Formulas in that worksheet populate, the next work sheet populates but the last sheet doesn't. And it has removed the values from the last time I copied the formulas down again.

I hope this makes sense to someone..

Automatic calculation is on.

thanks

This is the formula if anyone is interested...

{=textjoin("," & Char(10), TRUE, IF(ISNUMBER(SEARCH(B2, 'Matrix'!$A$2:$a$356)),IF('Matrix'!$B$2:$MT$356=1,'Matrix'!$B$1:$MT$1,TEXT(,)), TEXT(,)))}

 

[mrhstn]

Look in the VB Editor (press alt+F11). On the left find your workbook; expand the tree if it's not already, then look through each module (including the ThisWorkbook module) for any code.

 

[bonusdias]

what code am I looking for? I don't know VB at all ...

 

[mrhstn]

Any code at all...

See it sounds like something is clearing a range in your spreadsheet, I'm not looking at your spreadsheet so it's a guess based on your description.

Whilst in the VB Editor put it in Break Mode, on the menu... Run >> Break

See if the same behaviour persists.

 

[bonusdias]

I can't figure that out.. sorry... VBA illiterate.. but what I did do was copy the worksheets into a new workbook and the sheet automatically updated.. but when i copy more sheets it goes back to not updating.. do you know if there is a limit on the number of sheets and calculations that can be done... seems weird that there would be but this is what it looks like to me right now.

 

[bonusdias]

when i say the only thing that works is copying down the formulas again, i mean reactivating the cell. the formulas haven't been deleted they are just not calculating and showing the values.

 

[mrhstn]

Check out the specifications and limitations of Excel

Excel specifications and limits - Excel