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

Bill Nicholas

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.

Ramzi K

@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

Bill Nicholas

@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

@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

Bill Nicholas

@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

@Bill Nicholas

Are all your columns in both sheets of type Date?

Bill Nicholas

@Ramzi K

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

Thanks for your support with this Ramzi!

Ramzi K

@Bill Nicholas

Glad to help.