Adding date ranges to column headers

Hello Smartsheet Community,

I saw something at the conference in November and I'm having a hard time remembering what it was called so that I can figure out how to use it, I think it was a "series". Here's my problem: I have several sheets that I am using to create "block based" schedules, each block is defined by dates, (in one of the schedules Block 1 is 7/1-8/14, Block 2 is 8/15-9/30, ect.). I have the column headers labeled as the date ranges and I'd like to pull information based on individual dates, so I need a way to say that the column labeled "7/1-8/14" is the one where those dates will pull from. I think it will need to be a helper sheet that defines them, but I'm hoping the community can help me puzzle this out as I am stuck.

Thanks all,

Mandy

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Cell linking

Accepted answers

Paul Newcome

In that case, you would reference the sheet that is "by resident". You would need to set up separate {Cross Sheet References} for each column. You can then do an INDEX/MATCH and use a nested IF to output the appropriate {Range} for the INDEX function.

=INDEX(nested_if, MATCH(Resident@row, {Resident Column}, 0))

IF(Date@row <= DATE(2026, 8, 14), {First Date Column}, IF(Date@row <= DATE(2026, 9, 30), {Second Date Column}, IF(Date@row <= DATE(2026, 11, 14), {Third Date Column}, IF(Date@row <= DATE(2026, 12, 31), {Fourth Date Column}, IF(Date@row <= DATE(2027, 02, 14), {Fifth Date Column}, IF(…………………)))))))

Combined formula:

=INDEX(IF(Date@row <= DATE(2026, 8, 14), {First Date Column}, IF(Date@row <= DATE(2026, 9, 30), {Second Date Column}, IF(Date@row <= DATE(2026, 11, 14), {Third Date Column}, IF(Date@row <= DATE(2026, 12, 31), {Fourth Date Column}, IF(Date@row <= DATE(2027, 02, 14), {Fifth Date Column}, IF(…………………))))))), MATCH(Resident@row, {Resident Column}, 0))

All comments

Yasar_smartsyncapp

You’re probably thinking of Control Center “series”, but that won’t solve this specific problem.

Smartsheet formulas can’t dynamically reference columns based on date ranges in the column header. They only work with fixed column names. The cleanest workaround is exactly what you’re thinking: a helper setup where each row has Block Start Date, Block End Date, and the value you want to return, then use a formula to check which date falls between the start and end. In practice, this usually means restructuring the data so blocks are rows instead of columns. It’s not as slick as it sounds from the conference demo, but it’s the only reliable way to make date-based lookups work in Smartsheet today.

Paul Newcome

@Mandy Ford Are you able to provide screenshots for context?

Paul Newcome

@Yasar_smartsyncapp What is a Control Center "series"?

Mandy Ford

Smartsheet community - blocks to dates.png

Here's a photo of the template I'm working with. The names of the residents pull in from another sheet that has the residents' schedules listed by their names, so we have this one that is "By Service" view and then we have another one that is "By Resident" view. The column headers (the block start and end dates) are the same on both of these sheets.

Paul Newcome

What about your source data? I'm not sure I follow exactly what you are trying to do and what your logic is.

Mandy Ford

Well, there's multiple things that I'm attempting to pull in for this. I created these two sheets because I needed a solution to the errors that we previously had in our scheduling due to the needs of the services while ensuring that residents all have a full year of schedules. Previously, we had an excel workbook that we would have the two schedules on one tab is "by resident" where the residents can view their individual schedules for the year and we would manually add their names to the "by service" tab so that the services can know which residents are working with them for the full year, this second view is also necessary for us to ensure that the services are staffed appropriately throughout the year as we are scheduling. Being humans, we always had errors between these two views. I've created these two sheets and used formulas to pull the resident names from the "by resident" sheet into the "by service" sheet so that we no longer have human error and will see the accurate schedules from both views. Using dynamic dropdowns the residents are scheduled to services for each block and then the formulas automatically pull the resident names into the "by service" sheet so we can see both as we are scheduling to highlight what services are still available to schedule residents into for each block.

What I'm trying to do now is pull in our time off request process into this as well. We have an established form for residents to request individual days off and in this form it's important to know what service they are on so that the request can be sent to the appropriate scheduler. The problem with this is again is that humans make mistakes… often. Currently we ask the submitter to identify which service they are on during the time they are requesting off, and so far there are many many errors in their submissions which delays the process greatly and requires the admins to review each submission before they send to the schedulers for approval. What I would like to do is automate identifying the service they are on by using a formula to pull it from this source document.

My ultimate goal is this: When a resident submits a time off request (say "Resident 1" would like a vacation day on 8/10/26 ), instead of asking which service they are on, I would like a formula to look at this sheet (or a helper sheet) and say "Resident 1" is on "Service 4" during the 7/1-8/14 Rotation block.

I feel like this is possible somehow, but I can't quite reach a solution. Another option I've considered is using an API from another system that also has the resident schedules, but that would require more people in my institution to collaborate on.

Paul Newcome

Are you able to provide screenshots of the second sheet? Mock data is fine. I'm just having a little trouble visualizing what you are currently working with.

Mandy Ford

Definitely! this is just a few filled out so far, but here's the other sheet that pulls into the previous photo.

Smartsheet community - blocks to dates, by resident.png

Paul Newcome

Ok. I see both sheets. It looks like the second sheet is being populated as expected.

So from what I understand…

Your residents are submitting a form with a single date.

On this submission sheet, you want to reference that date and see where they are working based on where that date falls within the various column headers.

Is that correct so far?

Mandy Ford

Paul Newcome

=INDEX(nested_if, MATCH(Resident@row, {Resident Column}, 0))

Combined formula:

Mandy Ford

Wow! yes! this is it! wow. Thank you so much for your time and attention. This is exactly what I needed. Thank you!

Paul Newcome

Happy to help. 👍️

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.