Formula to automatically link Week to Date

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?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    thinkspi.com

Answers

  • Paul Newcome
    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))

    thinkspi.com

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

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    thinkspi.com

  • Perfect, thanks for your help on this Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com