# Formula to automatically link Week to Date

Options
✭✭✭✭

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?

Tags:

## Best Answer

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Answer ✓
Options

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.

• ✭✭✭✭
Options

Perfect, thanks for your help on this Paul.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!