Week number @row function

ginamt3

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

Genevieve P.

Hi @ginamt3

My apologies! There is a way to auto-fill a specific day of the week - it still requires a bit of manual work, but it's much easier than selecting the same day of the week in each individual cell. You can use our drag-fill function to repeat a date pattern, such as the same day each week.

Please see the section called "Use Drag-Fill with Dates" in our Help Center article, here: https://help.smartsheet.com/articles/2477471-dragfill

Thanks,

Genevieve

Genevieve P.

Hi @ginamt3

It looks like you just need to update the column name in the example [Week Column] I used. Your week column is called "Week", so you don't even need the square brackets.

Try this:

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

You'll know it's referenced the correct column because "Week@row" will light up in a specific colour. Let me know if this works!

Cheers,

Genevieve

ginamt3

Perfect! thank you.

Genevieve P.

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

Genevieve P.

Hi @ginamt3

My apologies! There is a way to auto-fill a specific day of the week - it still requires a bit of manual work, but it's much easier than selecting the same day of the week in each individual cell. You can use our drag-fill function to repeat a date pattern, such as the same day each week.

Please see the section called "Use Drag-Fill with Dates" in our Help Center article, here: https://help.smartsheet.com/articles/2477471-dragfill

Thanks,

Genevieve

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.

Genevieve P.

Hi @ginamt3

It looks like you just need to update the column name in the example [Week Column] I used. Your week column is called "Week", so you don't even need the square brackets.

Try this:

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

You'll know it's referenced the correct column because "Week@row" will light up in a specific colour. Let me know if this works!

Cheers,

Genevieve

ginamt3

Perfect! thank you.