Excel formula not updated automatically after adding sheet with the name that formula expects - how to fix it ?

[PaulskinX1]

Hi Dear all,

I have excel workbook (with vba procedures in it) with 3 tabs - lets call it Control model.
Each of the tab in Control model have formulas that are linked to the various sheet names that are not present in the Control model but are on other excel output (Input data -which has 7 sheets). The formulas in 3 tabs of Control model are holding sheet names of those present in Input data.

My Control model creates output (new excel book) that inserts 3 tabs from control model (with formulas referencing to sheet names of Input data) and Input data file. So I have a final file with 10 sheets in it ,however my formulas in 3 tabs from Control model will not update until I actually go to a specific cell and click after last element of formula and press enter.

Is there a way of fixing it with VBA or it is just inner excel characteristic that formula will not work if it reference to data that is not present in the same workbook at the time of formula creation?

 

[jasonb75]

or it is just inner excel characteristic that formula will not work if it reference to data that is not present in the same workbook

Anything that returns a #REF error will not have valid precedent cells to trigger calculation.

The easiest workaround is to use find and replace = with = which will effectively re-enter every formula in the specified range.

 

[PaulskinX1]

thanks Jason, this is exactly what i have done. I created a small vba procedure that replace '=' sign while seeing initially formulas with '$$$' as a placeholder for '='.

Seems to be working ok for now.