Meeting Minutes formula - automate to match meeting #

RiKi · 03/18/24

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.

Jason Albrecht · 03/19/24

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 · 03/19/24

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

Uploaded Mar 19, 20246.23 KB

240319 SS MM devt_extracted data from range.png

Uploaded Mar 19, 20243.82 KB

Jason Albrecht · 03/19/24

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 · 03/21/24

240321 MM filter devt with rows_01.jpg

Uploaded Mar 21, 2024131.84 KB

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.