# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  How to automatically refresh formulas?

## berti

I have Sheet1 with "formula"
=MySheet!A1
in e.g cell A1.
Because MySheet does not exist in this workbook, I get #REF error.
Later when I add MySheet, the #REF error is still there. 
But if I select A1 in Sheet1 and then click somwhere in the formula editor line and press ENTER, then A1 changes to 0 (or existing A1 value in MySheet)

I wonder how to automatically refresh some/all formulas in Sheet1 that reference to MySheet, after MySheet is inserted.

Please, see also attached file.

----------


## BigBas

This might help (I'm not sure).  Pressing the F9 key on your keyboard recalculates the sheet for you.  That should get rid of the error.

Try it and report back.

----------


## berti

No, the only thing that works right now is to go to Formula bar and there press enter to confirm formula again. But do not want to do it manually for all cells.
I tried to record a macro what it does is the following

  Range("A1").Select
  ActiveCell.FormulaR1C1 = "=MySheet!R[1]C"

that is, it creates formula for that cell, not refresh it. Cause if I want to run this macro on another sheet, I will not get the real formula, but hte one hardcoded in macro

----------


## arthurbr

This way seems to work
=INDIRECT("mysheet!"&"A1")

----------


## berti

> This way seems to work
> =INDIRECT("mysheet!"&"A1")



It really does! Thank you! :Smilie:

----------


## arthurbr

Glad to help and thx for the feedback

----------


## sfsalvo

Here's a macro that bangs through all the formulae in all the sheets in the Active Workbook and forces an update.  It's like so extremely cool I can't hardly even believe it.  

It resolves all the annoying #NAME? and #REF! errors before your very eyes, unless the formula is actually incorrect.  It only touches cells with formulae.  




```
Please Login or Register  to view this content.
```


I got bits and pieces of the code from hither and thither in the Great Encyclopedia of the Community of Coders.  I don't wear a hat but if I did, it would be off to them.

----------


## PrizeGotti

> Here's a macro that bangs through all the formulae in all the sheets in the Active Workbook and forces an update.  It's like so extremely cool I can't hardly even believe it.  
> 
> It resolves all the annoying #NAME? and #REF! errors before your very eyes, unless the formula is actually incorrect.  It only touches cells with formulae.  
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ...



Works great if you don't have many formulas. When you have hundreds/thousands of cells that contain formulas, you might need to set a day aside while it works through.

----------


## THOMPDRE

Below is updated to turn off screenupdating, which will significantly increase performance. Worked in about two minutes for >1500 linked cells using indirect function.



```
Please Login or Register  to view this content.
```

----------


## alea

Thanks for the code to refresh formulas, very useful!

----------


## sujayborude

Thank you very much for the code... its is really very useful...

----------


## Tb0ne07

How would I rewrite the macro above if I just want to refresh the formulas on the active sheet. In other words, I want to put a button on the active sheet that when pressed, would refresh all formulas on that sheet only. Sorry, probably a simple answer but I am new to the VB language.

Thanks.

----------


## THOMPDRE

I believe you would get rid of the "


```
Please Login or Register  to view this content.
```


" portion, and 


```
Please Login or Register  to view this content.
```


Basically that would eliminate the loop through every worksheet in the workbook and activesheet is a built in property.



```
Please Login or Register  to view this content.
```

----------


## willy-woo

Hi All,

Sorry to resurrect this old thread.

This is a really helpful bit of code, so thanks for posting it up!!

I'd like to know if it is possible to set it to only update one cell in each worksheet, A1 for example?

Basically I have a workbook with about 50 worksheets and I have a bit of code that pulls the colour as well as the text through to linked cells, but need to refresh one cell in each worksheet for it to update.

The posted formula works perfectly as it is bt takes a couple of minutes to do it, if I could only update one cell per worksheet it would be much quicker.

I know 90 seconds isn't a massive amount of time, but every little helps ay.

Thanks very much for your help.

----------


## THOMPDRE

You would need to change the rRange variable as follows:



```
Please Login or Register  to view this content.
```


At least that's what it would be for A1. You could also use the following:



```
Please Login or Register  to view this content.
```

----------

