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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!