Share via

Can I retain formula results if background data changes?

Jeanne Meadows 0 Reputation points
2024-07-11T20:27:21.42+00:00

To Note I tried like 30 times to post this in the Excel Forums and keep getting some random error.

I have a workbook with lets say 4 sheets. 

A-Coversheet       B-Requirements Tracker       C-Services      D-Software

Sheets A, C, and D have rows designated FY24 which cells have formulas which pull data from sheet B for the current fiscal year. 

Sheets A, C, and D have rows designated FY25-FY30 awaiting formulas which will pull data from sheet B when we move into the next fiscal year. 

This means the cells currently holding FY24 data will contain FY25 data after October 1st. 

Is there a way to ensure the current FY24 rows on sheets A, C, and D retain the FY24 data even after sheet B updates to FY25?

 

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,681 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,709 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 2,511 Reputation points
    2024-07-11T22:40:27.61+00:00

    After the last FY24 data has been entered on B and sheets A, C, and D have the final numbers:

    • On sheet A, select all the cells that contain the FY24 data.
    • Click on Copy on the Home tab or type Ctrl-C
    • Click on the arrow to open the Paste sub-menu and select the first Paste Values icon, or type Alt-E->S->V->Enter, to replace the formulas with the computed values.
    • Repeat for sheets C and D.

    The cells will no longer contain formulas so the values will not change when sheet B changes.

    0 comments