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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @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, "") 
    

    PMP Certified

    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

  • Bassam Khalil
    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.

    PMP Certified

    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
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Tammy Luther

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

    PMP Certified

    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"

  • Andrée Starå
    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!

    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.

  • Bassam Khalil
    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

    PMP Certified

    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
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @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, "") 
    

    PMP Certified

    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"

  • Tammy Luther
    Tammy Luther ✭✭✭✭

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Tammy Luther

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

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!