Index Match formula that I can drag down to show multiple rows with that have the same date.

HI there,

I'm trying to make dashboard for my team leads that displays specific stores have an install scheduled for todays date.

The top row of the sheet contains today's date:

I've created an Index match formula that references a separate sheet that contains a Store Number column & an Install Date Column

When I drag the formula down, the next row continue the sequence of stores for this date.

How can I fix this formula so when I drag the formula down to the next row the remaining stores that match this particular install date populate?

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Bryan G.

May I assume you are dragging the formula down the row because of your absolute row reference to the date?

You can create a Summary field reference (in the right hand ribbon down your sheet) and then use that in your formula so you can use the column formula function.

For your summary field, (you can call it whatever you like, I will call it Today's Date as an example), make the field a date column. Add this as your formula

=Date1

Then, in your formula for the Index/Match

=INDEX({ZCV Deployment Tracker Range 1}, MATCH([Today's Date]#, {ZCV Deployment Tracker Range 1},0))

This will allow you to make the formula a column formula which will remove the need to drag the formula. If you are unfamiliar with column formulas, this article may help. Note the article explains how to edit a column formula once one is generated

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Will this work for you?

Kelly

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.