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

jcabaniss

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?

Lucas Rayala

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

Lucas Rayala

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

jcabaniss

Thank you, this did work.

May I ask, is the way this works is that you're providing a range but the range is just the one column in the brackets with the "1" being the row? Just trying to make sure I understand since this seems like it will come in handy.

Thank you so much for the help!

Lucas Rayala

Hi @jcabaniss, yes exactly.