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

Options

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?

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

    Will this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!