Forum Discussion

CremeStout's avatar
CremeStout
Copper Contributor
Jan 09, 2026
Solved

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

community
  • NikolinoDE's avatar
    NikolinoDE
    Jan 10, 2026

    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:

    1. Select multiple sheets
    2. 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)

    1. Select all target sheets
      (Click first → Shift-click last)
    2. Click the destination cell (e.g. B22)
    3. Enter:
    4. =Aux!B22
    5. Press Enter
    6. Ungroup sheets

    This inserts the formula into every selected sheet
    All sheets will now update automatically

     

    Verify 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.

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 10, 2026

    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:

    1. Select multiple sheets
    2. 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)

    1. Select all target sheets
      (Click first → Shift-click last)
    2. Click the destination cell (e.g. B22)
    3. Enter:
    4. =Aux!B22
    5. Press Enter
    6. Ungroup sheets

    This inserts the formula into every selected sheet
    All sheets will now update automatically

     

    Verify 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.

    • CremeStout's avatar
      CremeStout
      Copper Contributor
      Jan 11, 2026

      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

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Jan 10, 2026

    Hello CremeStout​, 

    You’re running into the difference between pasting values and pasting formulas in Excel.

    When you use Paste Special → Values, Excel writes the number into each sheet. That’s just a snapshot — it will never update again. To keep things dynamic, you need the formula itself (=Aux!B22) in those cells, not the value.

    How to make it update across sheets:

    In your Aux sheet, put your calculation in A22 (for example =SUM(B1:B3)). Decide where you want that result to show in other sheets (say B22). Select all the target sheets at once (hold Ctrl or Shift while clicking their tabs). In the active sheet, type:

    =Aux!A22

    into cell B22. Because the sheets are grouped, Excel will enter the same formula into B22 of every selected sheet. Ungroup the sheets. Now, whenever Aux!A22 changes, all linked cells update automatically.

    If you want automation, you can also use a simple VBA macro to push the formula into every sheet:

     

    Sub LinkAuxToSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Aux" Then
ws.Range("B22").Formula = "=Aux!A22"
End If
Next ws
End Sub

     

    This way, you don’t have to group sheets manually — the macro writes the formula into B22 of every sheet except Aux.

    Bottom line: Don’t paste values. Enter or push the formula itself (=Aux!A22) into each sheet. That keeps the link live, so any change in Aux flows through to all the other sheets.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 09, 2026

    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?

    • CremeStout's avatar
      CremeStout
      Copper Contributor
      Jan 10, 2026

      I have those settings you mentioned. no go

      windows 11, Microsoft office 2024 purchased version