Formula to automatically link Week to Date

Emmet McKenna

Hello, we have individual production schedule sheets where we assign hours against weeks. We want to create a column that will autofill with the Date it is starting based on the first Week Number column that has hours in it (example attached). We have this working with a long formula:

=IF([wk 23]@row <> "", "06/06/22", IF([wk 24]@row <> "", "13/06/22", IF([wk 25]@row <> "", "20/06/22", and so on ....

But we are wondering if there is a simpler way to do this than having to type in every column name and corresponding date as per the above formula? Would it be possible to have a helper sheet with Wk No. and Date which can be easily filled in and then we could use a formula that IF the Week No. column in the original sheet is filled in, then it references the same Week No. in the helper sheet and inputs a Date?

Date to Schedule.jpg

Helper Sheet.jpg

Paul Newcome

Try this:

=IF(COUNTIFS([wk 1]@row:[wk 52]@row, @cell <> "") <> 0, INDEX({Helper Sheet Date Column}, MATCH(VALUE(LEFT(JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!", FIND("!", JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!") - 1)), [wk 1]@row:[wk 52]@row, 0)))

Basically we count how many in the row are not blank. If that number is not zero (meaning at least one is not blank) then we run the INDEX/MATCH.

Paul Newcome

Using the helper sheet you would have something like this:

=INDEX({Helper Sheet Date Column}, MATCH(VALUE(LEFT(JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!", FIND("!", JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!") - 1)), {Helper Sheet Week No. Column}, 0))

Emmet McKenna

Hi Paul, thanks for the formula suggestion, I've inputted it into one of the Production Schedule sheets and referenced the Date and Week No. columns from the Helper Sheet, and have the [wk 01]@row:[wk 52]@row, linking correctly as far as I can see but it is just showing up as #NOMATCH, the cells in the Week No. Column are all labelled as wk 01, wk 02, etc. Any ideas why no match is showing up? See below screenshot of updated production schedule.

=INDEX({Helper Sheet Date Column}, MATCH(VALUE(LEFT(JOIN(COLLECT([wk 01]@row:[wk 52]@row, [wk 01]@row:[wk 52]@row, @cell <> ""), "!") + "!", FIND("!", JOIN(COLLECT([wk 01]@row:[wk 52]@row, [wk 01]@row:[wk 52]@row, @cell <> ""), "!") + "!") - 1)), {Helper Sheet Week No. Column}, 0))

Paul Newcome

Sorry about that. I had set up the test on the same sheet as the data and mixed up a reference when converting it into something you could use. Try this:

=INDEX({Helper Sheet Date Column}, MATCH(VALUE(LEFT(JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!", FIND("!", JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!") - 1)), [wk 1]@row:[wk 52]@row, 0))

Emmet McKenna

Paul, that great, it's working now, thank you! One small issue is that for any blank rows below it is defaulting to 03/01/22 which is [wk 01] (even though there are no figures in that Column) is it possible to have this as blank if nothing is showing? I tried changing it around myself but can't get it to work.

Paul Newcome

Try this:

=IF(COUNTIFS([wk 1]@row:[wk 52]@row, @cell <> "") <> 0, INDEX({Helper Sheet Date Column}, MATCH(VALUE(LEFT(JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!", FIND("!", JOIN(COLLECT([wk 1]@row:[wk 52]@row, [wk 1]@row:[wk 52]@row, @cell <> ""), "!") + "!") - 1)), [wk 1]@row:[wk 52]@row, 0)))

Basically we count how many in the row are not blank. If that number is not zero (meaning at least one is not blank) then we run the INDEX/MATCH.

Emmet McKenna

Perfect, thanks for your help on this Paul.

Paul Newcome

Happy to help. 👍️