Best Of

That did the trick!! Thank you so much!

This is not possible natively in Smartsheet, however @SSFeatures has a paid browser extension that does just this!

@Tamsyn Happy to help. 👍️

Something to note:

I frequently use INDEX/COLLECT as @sharkasits mentioned, but there are times where an INDEX/MATCH/MATCH as you have is in fact appropriate.

INDEX/COLLECT is great if you are pulling from a single column but need to match on multiple columns.

INDEX/MATCH/MATCH is more for referencing an entire table where you need to match on both the horizontal and vertical axis.

INDEX/MATCH is for pulling from a single column (or row) while also matching on a single column (or row).

INDEX/MATCH/MATCH: You want to be able to pull from every row in columns 16 through 19 based on a value in Column 15 as well as a value across the top row.

=INDEX({Columns 16-19}, MATCH(2, {Column 15}, 0), MATCH("B", {Row 1}, 0))

INDEX/COLLECT: You want to pull from column 7 based on a match in both columns 15 and 16.

=INDEX(COLLECT({Column 17}, {Column 15}, @cell = 3, {Column 16}, @cell = "B"), 1)

INDEX/MATCH: Similar to INDEX/COLLECT but you only want to match on a single column.

=INDEX({Column 17}, MATCH(3, {Column 15}, 0))

INDEX/MATCH across a row: You can also use INDEX/MATCH to pull from across a single row and use the MATCH in the third portion of the INDEX function. Similar to INDEX/MATCH/MATCH, but the first MATCH is replaced with a 1 (row 1) since there is only 1 row being referenced.

=INDEX({Row 2}, 1, MATCH("C", {Row 1}, 0)

Hi @L Barry

I'd be happy to help you with an INDEX(MATCH(MATCH! 🙂

This is the structure of an INDEX function:

=INDEX(range, row number, column number)

It essentially looks at a range of cells and uses the row and column numbers to return a single cell value. In most INDEX(MATCH formula combinations we ignore the column number part because the first range is simply one column, and we use MATCH to find the row number.

However in your instance, you need to input a MATCH in both the Row place and the Column place of the function to find the correct cross-section and return the right cell, does that make sense?

The first part of the formula will be a range that covers all of the columns needed for your formula. For example, if your sheet has 3 columns with names in that top row, then it would look across all 3 columns.

=INDEX({Table of Names 06128204},

Then the first MATCH is looking to find what row to bring back. In this case, it's looking for the Client name to match across both sheets:

MATCH([Client Name]@row, {Client Name 06128204}, 0),

Then the second match is looking for what column to find data in, now that we know the row. In this case, we need to find the Role that's listed in the TOP ROW of your source sheet. That means the {range} is simply the top row, across all the columns needed (in my example this range would be 3 cells: the top 3 cells of the sheet):

MATCH([Assigned to Role]@row, {Roles 06128204}, 0))

Does that help explain the breakdown?

Cheers,

Genevieve

Hi @Sam Swain

To find the true booking overlaps, you can use a helper column with a formula that checks for the same date, bay, and overlapping times. I would try the below formula

=IF(AND([Date Of Parking]@row = [Date Of Parking]1, [Bay Number]@row = [Bay Number]1, OR([Start Time]@row < [End Time]1, [End Time]@row > [Start Time]1)), "Clash", "No Clash")

Smartsheet doesn't have a way to make a booking system that prevents overlaps automatically, but there is some advice you may find helpful in these posts:

Booking System in Smartsheets Forms — Smartsheet Community

Meeting Room Booking System — Smartsheet Community

Need formula to indicate double booking on the same date. — Smartsheet Community

Want to flag overlapping dates AND times — Smartsheet Community

Flagging a Date and Time Overlap — Smartsheet Community

You could create a separate sheet that is populated with formulas like Countifs, Index+Collect and/or Join+Collect to see what date & time ranges have more than one value? Eg. one row per date, one column per hour of the day, formulas looking at the main sheet to search the rows containing that day+time value. You'd want Collect or Countifs so you can provide multiple criteria looking at multiple columns.

COUNTIFS Function | Smartsheet Learning Center

INDEX Function | Smartsheet Learning Center

JOIN Function | Smartsheet Learning Center

COLLECT Function | Smartsheet Learning Center

Formula combinations for cross sheet references | Smartsheet Learning Center

Hi SherryFox,

I believe the formula you're attempting has some issues, and I have a revision that should fit your criteria.

Here’s an updated version of the formula to calculate the "Workflow Follow-up" date:

=IF(AND([Certification Complete]@row <> "Green", NOT(ISBLANK([Workflow Initiated]@row)), [Workflow Initiated]@row <= TODAY(-14)), TODAY(), "")

Explanation:

1. Condition for non-green certification:
   • [Certification Complete]@row <> "Green" ensures the certification is not marked as complete (Green).
2. Condition for Workflow Initiated:
   • NOT(ISBLANK([Workflow Initiated]@row)) ensures there is a date in the "Workflow Initiated" field.
3. Condition for 14 days elapsed:
   • [Workflow Initiated]@row <= TODAY(-14) checks if the "Workflow Initiated" date is more than 14 days old.
4. Populate "Workflow Follow-up":
   • If all the above conditions are met, it populates with TODAY() (current date). Otherwise, it remains blank ("").

Implementation Steps:

1. Copy the formula above.
2. Add it to the "Workflow Follow-up" column as a column formula or in the specific rows where you need it.
3. Ensure that the "Certification Complete" column uses the exact status names (e.g., "Green", "Yellow", "Red").

If this doesn't work as intended, let me know, and we can adjust further!