I need help with a formula that references one fixed date

Cat.Cosby ✭✭✭✭
edited 01/11/24 in Formulas and Functions

I want to create a formula with a fixed date at the top of the sheet. There are subtasks with actions that must be completed a specific number of days before this event. Each row of tasks corresponds to a different timeline.

Currently, my formula is =[Date of Event]@row - [Days Before Event]@row. However, this necessitates dragging down the Date of Event for all 251 rows, which is not practical for a template designed to accommodate various events. Using the @row will not work since I am only referencing "Task6"

I tried =Task6 and tried to right click to convert to a column formula to pull the date into the entire column. It would return an error since an absolute value is not allowed for column formulas.

Does anyone have any ideas on how to have the "TASK6" pull into the entire column "DATE OF EVENT"?


Best Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 01/12/24 Answer ✓

    Hi @Cat.Cosby Okay, I realized where we goofed. Your "date" reference in row 6 isn't actually formatted as a date, it's formatted as text. The easiest way to fix this is to have people enter the date on the Sheet Summary tab, in that entry. BUT, make sure that entry in the Sheet Summary has the property of "date". Try removing the formula, changing the property of the cell to Date, then entering the date you need manually. For now, also make sure the Date of Event column has the property type "Date", but you can probably remove this column and just reference the Sheet Summary value in your primary calculations.

    You might find using the Sheet Summary tab is a handy place to have people enter all their variables -- it acts like a form. As you now know, you can reference the cells within the Sheet Summary in regular sheet formulas using a hashtag.

  • Cat.Cosby
    Cat.Cosby ✭✭✭✭
    Answer ✓

    @Lucas Rayala I appreciate your help so very much!!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!