Auto Propagating Formulas
I have two matching sheets one that captures baseline dates, the other with current projections for many (~100 milestones). I would like to generate a third sheet that has the metrics of #days difference between the baseline and current dates. I can do this using "netdays" and adding references from the two other sheets. My question: is there a way to 'drag' the formula (like in excel) to all the other cells (all three sheets are the same format) so I don't have to manually build the formula for each cell?
Best Answers

Thanks so much!
This works well for the first column but doesn't seem to be updating to reference the new columns as I drag it across. Any ideas?
Thanks again so appreciate the help!

Cross sheet references do not automatically update with dragfill. You have 2 options.
First: You could manually update the appropriate cross sheet references.
Second: You could edit the cross sheet references to cover all columns that you are wanting to pull, insert a helper row into the working sheet that has the appropriate column numbers listed, then reference this row in the formula.
Answers

Are you able to provide screenshots of both sheets along with a manually entered third sheet that shows what you have and what you are trying to accomplish with sensitive/confidential data removed, blocked, and/or replaced with mock data as needed?

Hope this helps the three sheets have the exact same format (so am just including one screenshot). Looking to find the difference between the same dates on each page so I was using the following formula:
=NETDAYS({CURRENT Range 2}, {BASELINE Range 2})

The difference between the same date or the difference in the dates between the same activities?

Not sure I understand the question but I think it will be the same activity. One sheet tracks baseline and the other tracks current timing so I want to see how far off we are from baseline plan. So I want to see the difference between the two sheets for all datapoints eg activity 1, milestone 1; activity 1, milestone 2, etc... and activity 2, milestone 1; activity 2, milestone 2, etc...

So you have 2 sheets with dates. Baseline and actual. You want a third sheet that shows the difference. Is that correct?

Yes :)

Ok. If all three sheets are going to be set up exactly the same with the same row numbers containing the same activities and whatnot, then try something like this...
=NETDAYS(INDEX({Baseline Sheet First Date Column}, COUNTIFS($Description$1:$Description@row, OR(@cell = "", @cell <> ""))), INDEX({Actuals Sheet First Date Column}, COUNTIFS($Description$1:$Description@row, OR(@cell = "", @cell <> "")))
You would put this in row 1 of the first date column then dragfill this over to the rest of the columns and then change the cross sheet reference to look at the appropriate date columns for the other two sheets.
Then you can grab the first row of all four columns and dragfill down.

Thanks so much!
This works well for the first column but doesn't seem to be updating to reference the new columns as I drag it across. Any ideas?
Thanks again so appreciate the help!

Cross sheet references do not automatically update with dragfill. You have 2 options.
First: You could manually update the appropriate cross sheet references.
Second: You could edit the cross sheet references to cover all columns that you are wanting to pull, insert a helper row into the working sheet that has the appropriate column numbers listed, then reference this row in the formula.

Thank you so much! This worked perfectly, I appreciate your help!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!