Meeting Minutes formula - automate to match meeting #

RiKi
RiKi ✭✭✭✭
edited 03/18/24 in Formulas and Functions

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.


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. 

 

Answers

  • Jason Albrecht
    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.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • RiKi
    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.


  • Jason Albrecht
    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))

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • RiKi
    RiKi ✭✭✭✭
    edited 03/21/24

    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
    Jason Albrecht ✭✭✭✭✭✭

    try this:

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

    Hi @RiKi

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

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • RiKi
    RiKi ✭✭✭✭

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

    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)) 


  • Jason Albrecht
    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.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • RiKi
    RiKi ✭✭✭✭

    Jason,

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


  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Wonderful news!

    Thank you for letting us know.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!