What formula can I use to update the data based on a change in 1 cell with the date?

I have searched far and wide for a formula that may help me achieve what I need, to no avail.

I have a data table that is updated weekly to showcase upcoming launches based on their scheduled launch date. I currently manage this in Excel but the data is currently on a Smartsheet grid.

Ideally, I'd like to build out a similar table in Smartsheet that will update in real time and as we move through the weeks, would love to only change the date cells and not each formula currently using a count, distinct, collect with a nested "date(year,month,day)" to capture the right data that meets the criteria.

TIA for your help!

Find more posts tagged with

#formula

Accepted answers

Jessica M

@jjg279 - Thank you!! For some reason when I've tried that in the past it wouldn't work (definitely a user error). I was able to update my formulas to reference the cell instead of @row and worked like a charm!!!

All comments

jjg279

Hey Jessica, what type of formula are you actually looking to implement? If you are looking for a condensed table of what is happening this week you could try a report and filter it by "Launch Date" "is in the next (days)" and tell it seven days.

Jessica M

Thank you @jjg279 .

In excel where I currently update this launch plan, I have formulas in all of the cells, but when I update the date column to the new dates the other data will refresh based on the new date.

For example, I initially setup my formulas to include the date formula like this: (=COUNTIF({Launch Date}, DATE(2025, 7, 15)), however it's very time consuming to go into each formula to update the date for each column / row. I was hoping there is a faster way to make the update each week as I refresh my report.

jjg279

Hey Jessica! I think I see a bit. What I have done in the past for something like this is to either make an extra row or column that has my reference date(s) (usually the first row or a column on the far left). Then I only have to change the one cell to make all the other formulas change. So for the example you gave above the formula would be =COUNTIF({Launch Date}, $ReferenceCell$1) then you only need to change a single cell each week.

Would something like that work for you?

Jessica M

jjg279

Woot! Most welcome, and glad it is working!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.