Log In
Register
Home
September 26, 2023
Offline
I have a table with repeating monthly data. There are multiple columns per month (month value, variance, percent variance).
I want to copy formulas from January and paste them in February through December, but the relative references are not updating - they keep pointing to the same cells.
I've played around and discovered that if I drag a formula in the table, the relative references do copy and update as expected. But if I select Copy, and then Paste the formulas, the references do not change. Because the formulas change from column-to-column, simply dragging won't work in this particular case.
Attached is an example spreadsheet demonstrating the issue.
How do I copy/paste relative references in an Excel table?
Thanks!
Moderators
January 31, 2022
Offline
When you copy and paste form one column to another, structured table references do not behave like their direct cell reference counter parts. You would have to rearrange the order of columns and copy by drag and drop to achieve what you want. See attached.
Alternatively, use a hybrid form. I.e. both direct and structured references.
=E7-PRODUCTS[@[Target]:[Target]]
Not convinced, though, that this is good (best) practise.
1 Guest(s)
