Microsoft Excel - How do you auto-populate a formula in a column?

Question

Longevity, EmployeeID, StartDate, EndDate

I paste a bunch of data into the worksheet (cells B1-Dn), and I need the formula for A1-An (longevity) to automatically calculate the result for each employee that is entered.

I am using Microsoft Excel 2010.

Currently, the formula that I am using is:

=IF ( C1 , IF ( D1 , D1-C1 , TODAY()-C1 ) , "")

This works great, except that I have to manually add/remove cells with this formula in the A column to match the number of rows that are entered at any given time. This is very time-consuming, and difficult for Management to get their heads around when using this report. :)

Is there a way to say "Every row with data in it, gets THIS formula in column A"?

I've been researching this online, and found various answers. The most prominent answer was to use a drop-down-list with a default value, but I don't think that will work for a default formula.

Solution 1

Using Data tables should help you out with ensuring that your formulas cover the entire range.

Step one:

Paste your data (no formulas)

Step two: Go to insert -> table you should get a dialog that looks something like

Now put your formula in the adjacent column. You will notice that the table formatting will automatically expand into where your formula is, and that the formula will auto copy to the bottom of your data set.

Step three:

Copy a larger dataset into the table.... you will notice that the formula copies down with your new extended data set.

Note that the table won't be resized if you paste a smaller dataset.

Solution 2

If I understand correctly what you need, then maybe you should try this. In the every cell of column A (where you need the formula to be present if other columns contain data) add this formula :

=IF( AND( NOT(ISBLANK(B:B)),NOT(ISBLANK(C:C)),NOT(ISBLANK(D:D)) ),
     IF ( $C1 , IF ( $D1 , $D1-$C1 , TODAY()-$C1 ) , ""),"" )

To be more exact, you should add this formula in the A1 cell, and then auto fill all the A column.

Alternatively you could try setting the formula below in A1 cell and then auto fill the rest of the A column.

=IF( AND( NOT(ISBLANK(B1)),NOT(ISBLANK(C1)),NOT(ISBLANK(D1)) ),
     IF ( $C1 , IF ( $D1 , $D1-$C1 , TODAY()-$C1 ) , ""),"" )