Meeting Minutes formula - automate to match meeting #

Hi All,

Hope you're all well. I can't seem to figure out how to match the meeting date cell to the date that has been set in.

240318 Meeting Date to match meeting number.png

240318 Smartsheet AI formula generator_meeting date_attempt2.png

Here's the formula I set in but isn't working. I've also asked AI but they are unable to help.

=INDEX([Hidden Cols -->]@row:[Duration]@row,1,MATCH([Assigned To]$2,[Hidden Cols -->]$2:[Duration]$2,0,MATCH([Date Added]2,))

Any guidance is highly appreciated.

Find more posts tagged with

Accepted answers

All comments

Jason Albrecht

Hi @RiKi

Apologies for the following questions, but I'm a little confused by what you're trying to achieve.

I think you're trying to achieve the following:

=INDEX([Hidden Cols -->]$2:[Duration]$3, 2, MATCH([Assigned To]$2, [Hidden Cols -->]$2:[Duration]$2, 0))

The Syntax is INDEX(range, row_index, [column_index])

The range is between the column titles [Hidden Cols -->] row 2 and [Duration] row 3

You already know that you want to return the value (date) in second row of that range.

We now use Match to identify which column number to return.

The MATCH syntax is (search_value, range, [search_type])

The search_value is in the cell [Assigned To]$2

The range is between the column titles [Hidden Cols -->] row 2 and [Duration] row 2

You want the search type to be an exact match, hence we use the number '0'

When using AI to help with creating formulas, you can use the syntax to help.

For instance, you may use wording like:

Using INDEX and MATCH functions, where the range is from [Hidden Cols -->]$2 to [Duration]$3, the row_index is row 2 and the column_index uses the Match function to look at the value in [Assigned To]$2 cell, and match the exact value in the range between [Hidden Cols -->]$2 to [Duration]$2.

RiKi

Thanks for this Jason - appreciate it. The solution is quite close - what I want to happen is for Smartsheet to copy the cell value in row 3 that contains the date and link it to [Date Added] row 2 cell.

The current formula above copies the meeting author's name found on row 2.

For this example, the data in the range we would find will be the column [240214 MM 31B] then take row 3 underneath it which contains the date and link it to cell [Date Added] at row 2.

240319 Date added row 2 Linked cell to show date instead of author.png

240319 SS MM devt_extracted data from range.png

Jason Albrecht

Hi @RiKi

Please redo your first screenshot in your first post and include the row numbers please.

It sounds like we simply need to change the INDEX row_index from 2 to 3, like this:

=INDEX([Hidden Cols -->]$2:[Duration]$3, 3, MATCH([Assigned To]$2, [Hidden Cols -->]$2:[Duration]$2, 0))

RiKi

240321 MM filter devt with rows_01.jpg

Thanks Jason. I tried the formula but it didn't work. I am attaching a screenshot for reference. I wanted the row 3 date under 240214 column to be linked to the Date Added column, row 2 because the meeting number is 31B under Assigned To column row 2.

Jason Albrecht

try this:

=INDEX([Hidden Cols -->]$2:[Duration]$4, 4, MATCH([Assigned To]$2, [Hidden Cols -->]$2:[Duration]$2, 0))

Hi @RiKi

The author is actually on row 3, with dates on row 4.

RiKi

Thanks Jason, I tried the formula and it resulted in an invalid value.

240325 MM filter invalid.png

I tried the formula below but still resulted in the author, not the meeting date.

=INDEX([Hidden Cols -->]$2:[Duration]$4,2,MATCH([Assigned To]$2, [Hidden Cols -->]$2:[Duration]$2, 0))

340325 MM filter author not date.png

Jason Albrecht

Hi @RiKi

=INDEX([Hidden Cols -->]$2:[Duration]$4,3,MATCH([Assigned To]$2, [Hidden Cols -->]$2:[Duration]$2, 0))

Since the range is starting on row 2, it's the 3rd row down that has the date.

RiKi

Jason,

I tried the formula and it worked. Thank you so much!

Jason Albrecht

Wonderful news!

Thank you for letting us know.

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.