creating formulas for column and rows linking to other sheets

Tammy Luther

I could not find what I was looking for in other questions so I will try to pose what I need help with here.

I have one sheet that I have columns and rows summing totals from another sheet ... so totals by the week and the location. I now want to create a 7 day average on an existing sheet that I was doing a 5 day average. So at line 471, I've switched from 5 day avg to 7 day avg. I don't want to start a new sheet so hoping i can do it here. I created my first formula - =sum(link to other sheet cell for week and location)/7 and it calculates fine. I did manual formulas all the way across the row for the different locations (for that week). All good.

Now I'd like to find out how to copy down the formula for the row into the next week to grab the next week's data. When I try to do the copy with the + at bottom right, it just copies the cell but does not change the link to the next link in the original sheet. so all I'm seeing is a copy of the row above and not the next week. I can't use the convert to column formula because it will alter my first 470 rows of data that was calculated on a 5 day average, so I wanted to just copy and allow the formula to populate the next line with the next line from the other sheet.

How do I do this without having to manually write formulas for all cells in rows and columns from this point forward?

Does this make any sense to anyone?

TIA 😁

Bassam Khalil

@Tammy Luther

After or zoom meeting i solve it as per your requirements, please check it:

=IFERROR(SUMIFS({Chandler-Maricopa}, {Date To}, @cell = Date@row) / 7, "") 

Bassam Khalil

Hi @Tammy Luther

Hope you are fine, is it possible to share me as an admin on a copy of your sheet after removing any sensitive data and I will try to help you to solve this problem.

Bassam Khalil

Hi @Tammy Luther

Am waiting for you to share a copy of your sheet to create the formula for you.

Andrée Starå

Hi @Tammy Luther

I hope you're well and safe!

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

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

@Tammy Luther

Hope you are enjoying and spending good time, i created the formula for you in the first 3 columns highlighted with Yellow, please check it, also if you like i can change the whole configuration for you to do it as a column format formula so you will not need to drag the formula to create for new rows. 

=SUMIFS({Metrics - daily case changes Range 3}, {Metrics - daily case changes Range 2}, >=Date468, {Metrics - daily case changes Range 2}, <=Date474) / 7

Bassam Khalil

@Tammy Luther

After or zoom meeting i solve it as per your requirements, please check it:

=IFERROR(SUMIFS({Chandler-Maricopa}, {Date To}, @cell = Date@row) / 7, "") 

Tammy Luther

@Bassam Khalil - thank you for going above and beyond to assist! It is greatly appreciated. Glad we connected. :)

Bassam Khalil

@Tammy Luther

You are welcome and I will be happy to help you any time.