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?

Find more posts tagged with

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.