Forum Discussion
Want a formula result to update across Sheets?
I have figured out how to get a formula result to appear in multiple sheets.
The formula for example that gets pasted into sheets references a result!
I enter formula and (B5) is where the sum location of =sum(b1:b3) will be delivered'
I create a new formula that will reference previous calculation label this in cell B7
=Aux!B5
I use =Aux!B5 where Aux is the sheet where all calculations take place and this result will want to appear in selected work sheets
Then i select sheets (using Shift+ selected sheets) then in first sheet of selected sheets
I select a location and paste V (value) i get the result across all selected tabs.
Here is my question !
how do i get a location to update all worksheets with a value from a formula from sheet Aux?
I want to be able to change the Value in Aux (sheet where all calculations occur), then have that
new value appear where former locations were pasted the result. (this should put result in B22
Ex: i create a total =sum(B1:B3) (in A22 of the aux sheet ) that result i put in a new location with a
new formula so new location ( B22) formula =aux!A22
It is this location that if i change a value in origional EX: b1 from 3 to 5 the result is updated in B22
but it will not update the sheets with =aux!B22
hope not to confusing
What you’re running into is a values vs formulas issue, not a calculation or Excel version problem.
Short version:
If you paste values, they will never update.
To update across sheets, the cells must contain formulas, not pasted values.Let’s break it down clearly and then give you the correct way to do this.
What’s happening now (why it doesn’t update)
When you:
- Select multiple sheets
- Paste V (Paste Values)
Excel is doing exactly what you told it to do:
- It pastes the current number
- It removes the formula (=Aux!B22)
- There is no longer a link to Aux
So later, when you change Aux!B1:
- Aux!A22 updates
- Aux!B22 updates
- Other sheets do not update because they only contain a static value
This is expected Excel behavior.
What you actually want
You want:
- One calculation sheet (Aux)
- Many sheets that stay linked to it
- When Aux changes → all sheets update automatically
To do that, every target cell must contain a formula, not a pasted value.
Correct way to do this (step-by-step)
Keep your calculation in Aux
Example:
Aux!A22 = SUM(B1:B3)
Optional helper cell:
Aux!B22 = A22
Link other sheets with formulas (NOT values)
Option A — Use grouped sheets (best if layouts match)
- Select all target sheets
(Click first → Shift-click last) - Click the destination cell (e.g. B22)
- Enter:
- =Aux!B22
- Press Enter
- Ungroup sheets
This inserts the formula into every selected sheet
All sheets will now update automaticallyVerify it worked
Click any target sheet and check the cell:
- Formula bar should show:
- =Aux!B22
If you see a number instead → it was pasted as a value and will never update.
What will NEVER work (by design)
- Paste Special → Values
- Copy → Paste Values
- Drag-fill after pasting values
Once the formula is removed, Excel has no reference to Aux anymore.
If you need different sheets but same formula
If every sheet should pull from Aux but stay independent:
='Aux'!$B$22
Use absolute references so nothing shifts.
Summary
Method
Updates?
Paste Values
Never
Formula =Aux!B22
Always
Grouped sheets + formula
Best solution
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
4 Replies
What you’re running into is a values vs formulas issue, not a calculation or Excel version problem.
Short version:
If you paste values, they will never update.
To update across sheets, the cells must contain formulas, not pasted values.Let’s break it down clearly and then give you the correct way to do this.
What’s happening now (why it doesn’t update)
When you:
- Select multiple sheets
- Paste V (Paste Values)
Excel is doing exactly what you told it to do:
- It pastes the current number
- It removes the formula (=Aux!B22)
- There is no longer a link to Aux
So later, when you change Aux!B1:
- Aux!A22 updates
- Aux!B22 updates
- Other sheets do not update because they only contain a static value
This is expected Excel behavior.
What you actually want
You want:
- One calculation sheet (Aux)
- Many sheets that stay linked to it
- When Aux changes → all sheets update automatically
To do that, every target cell must contain a formula, not a pasted value.
Correct way to do this (step-by-step)
Keep your calculation in Aux
Example:
Aux!A22 = SUM(B1:B3)
Optional helper cell:
Aux!B22 = A22
Link other sheets with formulas (NOT values)
Option A — Use grouped sheets (best if layouts match)
- Select all target sheets
(Click first → Shift-click last) - Click the destination cell (e.g. B22)
- Enter:
- =Aux!B22
- Press Enter
- Ungroup sheets
This inserts the formula into every selected sheet
All sheets will now update automaticallyVerify it worked
Click any target sheet and check the cell:
- Formula bar should show:
- =Aux!B22
If you see a number instead → it was pasted as a value and will never update.
What will NEVER work (by design)
- Paste Special → Values
- Copy → Paste Values
- Drag-fill after pasting values
Once the formula is removed, Excel has no reference to Aux anymore.
If you need different sheets but same formula
If every sheet should pull from Aux but stay independent:
='Aux'!$B$22
Use absolute references so nothing shifts.
Summary
Method
Updates?
Paste Values
Never
Formula =Aux!B22
Always
Grouped sheets + formula
Best solution
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Ty as i revisited my aux sheet found some errors! fixed those then tried your solution.
the key appears to be after grouping tabs then enter formula =Aux!E22 in same cell reference of the result from Aux sheet into Group tabs.
your explanation of why my attempts did not work were correct and concise. basing on your suggestions it now functions as i wanted.
FYI: i build a yearly, with monthly tabs, expense predictor so in aux (being retired) I get fixed or annual adjustments) and those carry over into each month on certain dates.
these values only change begining in Jan.
Did not try the ='aux'!$E$22 could you explain how that could be a different solution?
ty again most helpful
from what I am reading that should work. make sure your workbook is set to automatically calculate:
Formulas -> Calculation Options -> Automatic
also, what version of excel are you using? and is it windows? mac? online?
I have those settings you mentioned. no go
windows 11, Microsoft office 2024 purchased version
