Cell Reference

Vesta

Ok, I tend to think I am someone decent in Smartsheet, but I am STRUGGLING with something so simple and would love some help...

Background - I have a project team and we use smartsheet forms for time tracking.  Every month we archive the previous month's data on a separate sheet.  However, to calculate metrics, I do a SUMIF (=SUMIFS({Jason Time Tracking Range 1}, {Jason Week}, "41") + SUMIFS({Jason Archive Range 1}, {Jason Archive Range 2}, "41") to pull from each person's archive and current month sheet to sum their hours (by week), and track it historically.  

My spreadsheet is set up where each column is a work week, with the cell below referencing the week number.  It works fine as is, but adding a new month turns into hours of me changing the week numbers after I drag the formula over.  I am certain there is a better way, but for the life of me I can't get it to work.

I tried adding a cell reference, but it only pulls over the exact cell, even though I haven't made it absolute.  What I would love is if I can set the formula up so that as I drag the formula over, it updates the reference to the different week number so I don't have to change it manually every month.  

I'm attaching a screenshot below.  Thanks for reading!

Andrée Starå

Hi Vesta,

If I understand your structure correctly, one way to simplify could be to exchange the week number in the formula and replace it with the cell with the week number from the first row.

Make sense?

Can you describe your process in more detail and maybe share the sheet(s) or some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD