Auto Propagating Formulas

Lisa M

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?

Lisa M

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!

Paul Newcome

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.

Paul Newcome

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?

Lisa M

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})

Paul Newcome

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

Lisa M

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...

Paul Newcome

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

Lisa M

Yes :)

Paul Newcome

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.

Lisa M

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!

Paul Newcome

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.

Lisa M

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

Paul Newcome

Happy to help. 👍️