One cell less Formula Smartsheet/Excel

[ctackett6407]

I'm finding myself copy and pasting things 1000's of times (literally) and I do not want to spend my whole weekend working on one spreadsheet lol

I'm using both Excel and Smartsheet so this is a formula from Smartsheet, but I want to know for both areas.

=SUMIFS({Resources_Required Data}, {Group_Name}, $[Primary Column]$391, {Weather Data}, $[Primary Column]$408, {Day of Week Data}, $[Primary Column]409, {ADD_TIMESTAMP Data}, >=DATE(2016, 1, 1), {ADD_TIMESTAMP Data}, <=DATE(2016, 12, 31))

Is there a way in Excel to lessen an anchored cell to be one less than another cell?

example, referencing the above formula I made.. is there a way to add something to the formula to have $[Primary Column]$408 be one less than $[Primary Column]409?

If I can figure that out then I'll be one step closer to not having to manually update 7 cells in each section.

 

[Snakehips]

ctackett6407,

It's not clear to me, how your data is set up.
Maybe you explore the possible use of using OFFSET and or INDIRECT
If as I suspect, your reference to 7 is to do with days of the week then maybe MOD() with a divisor of 7 might help.

Hope that helps.

 

[ctackett6407]

Thanks for the reply,

it's not really about the data it's about repeating the same step over and over for every section.

For each "Group" I paste my section then I have to go in and manually update the reference. I have a part in each formula that pulls in the "Group" name for that section so that has to be updated manually, but there is one spot where it's just 1 less than another cell and I was curious if there was a way that can be incorporated in the formula.

I made a little video clip

https://streamable.com/so9mn