Why do my cell formulas not paste when I copy and paste rows?

Chelsea Hirschton

I have a template, which I have spent a lot of time configuring. I have a set of default rows by product by tier. Once I know which products are launching in a release and what the assigned tier is, I go in and copy that list of deliverables for the given product. When I paste the rows my formulas (which have cell references) are not pasting correctly and I end up with UNPARSEABLE errors. I've tried paste special with "formats" and I get the same result. Given I have about 30 rows per product per tier all with set dates... makes the use of the template less useful given I have to reset all of the dates per product per tier. 😖

This is the formula in the default rows:

This is what gets pasted:

Chelsea Hirschton

And oddly, when I examined the cell below the one above it did paste accurately.... The difference appears to be that this formula references the cell directly above it, while most of my formulas reference cells in rows at the top of the sheet.

This is the next formula down that I copied:

And this is what pasted:

Sam M.

Hi Chelsea,

Maybe you can try Absolute reference in a cell.

For example maybe you can try this:   =$[Target Date]$123+30   or in the cell you specifically need.

The two $$ represent an absolute reference for column and row for the cell.

When you copy and paste the reference stays and if you drag-fill the reference also stays.

I did some testing:

Copied the first table without adding the $$ signs and it did not stay with the date in green:

I put the $$ signs and copied the table and the references stayed:

Hope it helps in some way,

Sam