creating formulas for column and rows linking to other sheets
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 😁
Best Answer
-
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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Am waiting for you to share a copy of your sheet to create the formula for you.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Bassam Khalil - thank you for going above and beyond to assist! It is greatly appreciated. Glad we connected. :)
-
You are welcome and I will be happy to help you any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!