I'd like to create a column formula that references a specific cell with acceptable syntax

I have a sheet that has a column of dates that autofill based on another date.

Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date.

My formula is currently: =[Target End Date]1 - ([Task Weeks Out]@row) * 7

The "1" references that column's first row entry which is the target end date for the entire project that all other dates autofill based on.

I can't seem to find a way to make this a column formula because it's referencing a specific cell. Is there any way to write this in a way that is compatible with column formulas?

Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    @jcabaniss , swap out this:

    =[Target End Date]1

    with this:

    =Index([Target End Date]:[Target End Date], 1)

    the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!