Week number @row function

Is there a more efficient way I could write this formula? I'm looking for the week number and I physically have to go in and change it for each cell. I would like so I can just copy down the formula.


Also, a more efficient way to report down the date for the start of the week without having to manually pick it for each cell

Best Answers

  • ginamt3
    ginamt3 ✭✭✭✭
    Answer ✓

    Perfect! thank you.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @ginamt3

    It looks like you already have the week number returned in the column on the left... because of this, all you have to do is change out the number in your formula to reference that cell @row (like you suggested!) Try this:

    =SUMIFS({MASTER SCHEDULE Range 4}, {MASTER SCHEDULE Range 5}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = [Weeknumber Column]@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019))


    Note that you will need to change [Weeknumber Column] to be the actual title of that column to the left in your screen capture. Then you can drag-fill down the formula and it will update automatically for every row/week number.

    For your second question, at this time there is currently no functionality to auto-fill the start date for each week (other than manually selecting that day). Please submit an Enhancement Request when you have a moment!

    Cheers,

    Genevieve

  • ginamt3
    ginamt3 ✭✭✭✭

    Thank you. I got the autofill to work.


    Im still having an issue with the @row function. I added like you suggested and I'm still getting unparsable.


  • ginamt3
    ginamt3 ✭✭✭✭
    Answer ✓

    Perfect! thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!