I have a table with live data in it that is calculated based on an underlying dataset and changes regularly.

I want to be able to copy this data into another table every Friday so that I can see the trends week on week. The only problem is that with the formula that I am using as the source data changes so does the data in my trend table. Is there a way that I can use a static value so that it wont change once the source data change like a "Paste Values".

The current formula I am using to pull the data every Friday is =IF(C3=TODAY(),A3,""), c3 is the date of that Friday. This works but I need it to paste a static value so is there a way to paste the value of A3 on that date instead of referencing what is currently in the cell.


You can use VBA / macros to record the actions of copying, pasting as values in a new sheet. There is no formula for "copy and paste" as that would imply that the formula in one cell can somehow affect the value of another cell (without a formula). Spreadsheets don't like that and makes for unpredictable and possibly circular calculations.

The non-VBA alternative is to use two separate files. Excel will ask you to update the external source every time you open it. You can decline every friday and break the link, saving as another file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.