Formula help

I have 2 smartsheets 1)Schedule and 2)current week schedule

both have a column for colleagues and columns with the current date (not a date field - needed to be text since the data in the actual cell is text) I have added a helper row and added the text date there as well


What I need to happen is for the information in the cells associated with the colleague in the Schedule to populate the same colleague row in the Current Week Schedule if the colleague name on the Schedule matches the colleague name on Current Week Schedule and the text date in row 1 on the Schedule matches the text date in row one on Current Week schedule. I only need to see the current week's data. Then I'll use the Current Week Schedule to automatically send a notification to the colleague to let them know what their schedule is (in additional to any changes to that current week)


I can get Index Match to work if I am not looking for the date to also match. I'm not sure how to use Index Collect but wondering if that might work.


Thanks for your assistance.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot for reference?

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭

    Keep in mind this is a week in progress

    Schedule - no date columns

    Current Week Schedule -


    I want the columns labeled Sun - Sat in the Current week schedule for the colleague to be populated from the columns labeled 12/12 - 12/19 or the same colleague in the Schedule.


    Keep in mind, the data that needs to be displayed in the Current Week schedule is only associated with the current week. The user doesn't want to remove the data from the Schedule so we need to be able to look ahead for the right data based on the current weeks dates

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭

    Any thoughts on this?


    Keep in mind this is a work in progress

    Schedule - no date columns


    Current Week Schedule -



    I want the columns labeled Sun - Sat in the Current week schedule for the colleague to be populated from the columns labeled 12/12 - 12/19 or the same colleague in the Schedule.


    Keep in mind, the data that needs to be displayed in the Current Week schedule is only associated with the current week. The user doesn't want to remove the data from the Schedule so we need to be able to look ahead for the right data based on the current weeks dates

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭
    Answer ✓

    I got it to work using an Index/Match function and using column numbers to reference information I want to populate from one smartsheet to another.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My apologies for the delay. My notifications stopped working for a little bit. I am glad you were able to get it working.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!