I need help with a formula that references one fixed date

Cat.Cosby

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"?

Lucas Rayala

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

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

Lucas Rayala

Hi @Cat.Cosby -- there's a few workarounds you can use to reference a single cell in a column formula. The easiest might be this:

Open your Sheet Summary tab and create an entry that either contains the date or a reference to that date (this can be =TASK6). I'll refer to this entry as "Date" but call it what you will.

You can reference data in the Sheet Summary tab using a #. The formula will then be:

=Date# - [Days Before Event]@row

Lucas Rayala

Oh, sorry, if you want every single line in "Date of Event" to be 2/1/24, you can still use the same trick with the sheet summary, just make the Date of Event column formula:

=Date#

Cat.Cosby

@Lucas Rayala Thank you! it half worked. It did fill in the entire column with the reference date of TASK6. (yay!!!!) however it proceeded to break my formula :-(

Lucas Rayala

Hi @Cat.Cosby -- by "break" are you referring to the Invalid values? Can you show what the formula is that is breaking?

Cat.Cosby

@Lucas Rayala sure! The formula is =[Date of Event]@row - [Days Before Event]@row. and the error is #Invalid Operation

Cat.Cosby

@Lucas Rayala The formula in the [Due Date] column turns into "Invalid Operation" when I convert [Date of Event] column to a Column Formula. It only works when its a cell formula

Lucas Rayala

@Cat.Cosby , check to see if you are creating an actual date in the Date of Event column — wherever you have a free space outside that column (you can create a temp column) point the formula:

=IF(ISDATE([Date of Event]@row), “yes”, “no”)

what’s it say?

Cat.Cosby

@Lucas Rayala #UNPARSEABLE

Lucas Rayala

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

@Lucas Rayala It worked!!!!!! Thank you so much!!!

Lucas Rayala

@Cat.Cosby -- fantastic! glad you're up and running.

Cat.Cosby

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