"Sprint#" dropdown single-selector that inserts Start Date & End Date

Options

Seeking help on crafting a sprint calendar widget.

I've set up a dropdown selector for "Sprint" - a single-select text cell with name: "Sprint 112" or "manual entry". I would love it if I could have the the selection in this cell fill the start and end date cells (or not, if "manual entry" is selected).

I'm using a multi-sheet approach, where my Project Sheet is referencing a Sprint Schedule sheet, which contains Columns: Sprint, Start Date, End Date.

My goal would be to reference the Sprint Schedule sheet from many project sheets, enabling me to update our sprint schedules in only one place.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Bill Nicholas

    Here's one way to do it:

    Sprint Schedule Sheet looks like this ...

    Project Sheet looks like this ...


    Sprint Column looks like this ...

    Sprint Start column formula (with external sheet references to Sprint Schedule sheet:

    =IFERROR(INDEX({Sprint Schedule Start Date Range}, MATCH(Sprint@row, {Sprint Schedule Sprint Name Range}, 0)), "")

    Sprint End column formula (with external sheet references to Sprint Schedule sheet:

    =IFERROR(INDEX({Sprint Schedule End Date Range}, MATCH(Sprint@row, {Sprint Schedule Sprint Name Range}, 0)), "")

    I hope this helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Bill Nicholas
    Options

    @Ramzi K

    Hi Ramzi... thanks for the awesome reply!

    I'm getting a #INVALID COLUMN VALUE error... further thoughts?

    I've tried your functions both in a Date cell and in a text cell just to test, get the same error in both.

    I have everything set up the way your screenshots show.

    I have a question (thought I've tried this both ways) -

    When specifying the colums (ranges) in my linked spreadsheet (Sprint Schedule), do I click the header (selecting the entire column)? or do I select a specific range of cells?

    Thanks for your continued assistance :)

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Bill Nicholas

    Can you show me the formula that's resulting in the error?

    Also to answer your second question, you would select the entire column.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Bill Nicholas
    Bill Nicholas ✭✭
    edited 10/19/20
    Options

    @Ramzi K

    =IFERROR(INDEX({Sprint Calendar Range 2}, MATCH(Sprint@row, {Sprint Calendar Range 3}, 0)), "")


    {Sprint Calendar Range 2} = 'Start Date' column

    {Sprint Calendar Range 3} = 'Sprint Name' Column

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Bill Nicholas

    Are all your columns in both sheets of type Date?

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Bill Nicholas
    Options

    @Ramzi K

    sure enough - project sheet date column wasn't 'date type'

    Thanks for your support with this Ramzi!

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Bill Nicholas

    Glad to help.

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!