How to use F2 + F9 + Enter for entire Worksheet

[jeffreyp]

I have three columns in a worksheet with formulas that resemble: ="+'"&$B5&"'!H5"
I can do F2, F9 Enter for each cell to get them to calculate but how can I do this for the entire worksheet at once?

 

[Joe4]

Welcome to the Board!

If you just press the F9 key, it should re-calculate your whole sheet at once.
Or, just turn on Automatic Calculations, and everything will automatically re-calculate every time the data changes.

 

[jeffreyp]

Thanks for the quick reply, Joe.

If I click F9 nothing happens. My workbook is already set up to calculate automatically. If I do F2, F9 Enter individually the cells update. I've unfortunately got several workbooks I need to do this for. Any other suggestions? I attached a picture of what the worksheet looks like.

 

[jkpieterse]

It looks like your formula "calculates" a valid off-sheet reference similar to:
+'Other Worksheet'!H5
Are you using that in an INDIRECT function by any chance?

To force a full recalculation you can press Control+Shift+Alt+F9

 

[jeffreyp]

It looks like your formula "calculates" a valid off-sheet reference similar to:
+'Other Worksheet'!H5
Are you using that in an INDIRECT function by any chance?

To force a full recalculation you can press Control+Shift+A

 

[jeffreyp]

Column B are static item skus that are also the names of the other tabs in the workbook. The formula in column C is :
="+'"&$B5&"'!A2"
When I do F2, F9 Enter the formula stays the same but the actual data from the tab noted in column B displays in the field.
I'm not using INDIRECT. Does that help?

 

[RoryA]

I suspect you want something like this:

Sub ConvertFormulas()
selection.formula = selection.value
End sub

after selecting all the cells.

 

[jeffreyp]

I suspect you want something like this:

Sub ConvertFormulas()
selection.formula = selection.value
End sub

after selecting all the cells.

Rory- is this something I would do in VBA? I've never used it before.

 

[jkpieterse]

If you change those formulas to this:

="='"&$B5&"'!H5"

you can do a copy, paste Values and after that do a Search and replace, searching for = and replacing with =

 

[Alex Blakenburg]

Doing that with the existing set up worked for me.
• Select formula area
• Copy Paste Values
• Find & Replace "+" with "="