How to add/delete columns while keeping pre-populated formulas intact?

Question

I am creating a timeline to be used by co-workers who are not very savvy with Excel. They wanted the Planned Start/End Dates to be calculated based off of one date that they enter, along with durations that they can edit. They do NOT want to edit the actual Planned Dates.

It is crucial that they are able to add/delete rows within the table I've created. However, every time I attempt to test that, the formulas I've entered to calculate the Planned Start/End Dates break.

I have tried entering the formulas directly into the table, and I have also tried entering the formulas alongside the table (but not within it) and referencing those cells to populate the dates.

I have also tried referencing the column's actual names instead of column letters.

Explanation of what each cell represents:

D7: Whether to calculate the dates based on Project Start Date or In Warehouse Date
D8: Specified date
Column E: Duration (Days)
Column F: Planned Start
Column G: Planned End

Planned Start Date Formula (very first row):

=IF($D$8="","",IF($D$7="Project Start Date",$D$8,IF($D$7="In Warehouse Date",G11-E11+1,"")))

Planned Start Date Formula (all rows after first row):

=IF($D$8="","",IF($D$7="Project Start Date",F11+E11,IF($D$7="In Warehouse Date",G12-E12+1,"")))

Planned End Date Formula (very last row):

=IF($D$8="","",IF($D$7="Project Start Date",F35+E35-1,IF($D$7="In Warehouse Date",$D$8,"")))

Planned End Date Formula (all rows before last row):

=IF($D$8="","",IF($D$7="Project Start Date",F34+E34-1,IF($D$7="In Warehouse Date",G35-E35,"")))

The formulas calculate the dates correctly. However, they break whenever I add or delete rows. When adding rows, it pushes the Duration reference so that it's always off by one row after the added row. When deleting rows, it completely breaks the formula and replaces the Duration reference with #REF.

I am somewhat familiar with VBA, so if I am able to somehow fix this issue using a Macro or VBA code, I'd be more than happy to try it.

Thank you!

Why are the dates in separate rows/looking at other rows? If you reference a cell and delete the cell, you will get the #REF error no matter what. This cascades down. The solution would be to make each row stand alone. — Evan Friedland
The dates are referencing separate rows depending on if they're being calculated on the Project Start Date or In Warehouse Date. If calculated on the Project Start Date, they calculate based on the very first planned date and trickle down sequentially. If calculated based on the In Warehouse Date, they calculate based on the very last planned date and trickle up sequentially. — travelbug928
Can you post an example picture of the sheet layout with fake information? It’s not clear for me what/where these different formulas are — Evan Friedland
Yes of course! My apologies in advance, I had no idea how to upload or embed images in a comment, so I just used TinyPic... link (dates based on Project Start Date)... link (dates based on In Warehouse Date)... I do believe I finally found a fix, though! I posted it in a separate answer on this question. — travelbug928

travelbug928 travelbug928 · Accepted Answer · 2019-04-17T12:31:41

I believe I found a fix for my question! I replaced all relative references with a combination of OFFSET and INDIRECT in the formulas, and now it's working! I left the formulas alone for the very first row for Planned Start Date, and the very last row for Planned End Date.

For Planned Start Date middle rows, here's what the formula looks like now:

=IF(OR($D$7="",$D$8="",INDIRECT("E" & ROW())=""),"",IF($D$7="Project Start Date",OFFSET(INDIRECT("F" & ROW()),-1,0)+OFFSET(INDIRECT("E" & ROW()),-1,0),IF($D$7="In Warehouse Date",INDIRECT("G" & ROW())-INDIRECT("E" & ROW())+1,"")))

And for Planned End Date middle rows, here's what the formula looks like now:

=IF(OR($D$7="",$D$8="",INDIRECT("E" & ROW())=""),"",IF($D$7="Project Start Date",INDIRECT("F" & ROW())+INDIRECT("E" & ROW())-1,IF($D$7="In Warehouse Date",OFFSET(INDIRECT("G" & ROW()),1,0)-OFFSET(INDIRECT("E" & ROW()),1,0),"")))

How to add/delete columns while keeping pre-populated formulas intact?

1 Answers