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
-
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.
Answers
-
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))
-
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))
-
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))
-
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.
-
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.
-
Perfect, thanks for your help on this Paul.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.5K Get Help
- 63 Global Discussions
- 46 Industry Talk
- 386 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 466 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
