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

PaulskinX1

New Member
Joined
Mar 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,410
Messages
5,636,118
Members
416,900
Latest member
Mokor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top