Cross sheet/ MATCH formulas

Hello everyone,


I am working on a few new smartsheets that tracks our sites incident reporting data.

I know I can work a COUNTIFS formula to do what I want, but i need the data in the summary to formulate based on the criteria at the top (circled below)

I need the 2 sheets to cross check each other and match weeks, periods and years.

So below, on the first top left "Accidents weekly total" table, The first aid box will need to count anything that is a first aid, that occurred in 2021, period 7, week 27.

, and for health care and lost time I need them to calculate the same, but for "Health care" and "Lost time" instead of "first aid", but also not equal to "Denied" in the claim status column


Here is the sheet I am looking to pull from. Claim status is where you would find the "denied" choice

I cannot show the data due to sensitive content


Once i know how to get the data to match between sheets, I should be good to go from there


TIA!

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Jessica Howell

    Hope you are fine, please call me on my Email because I need a sample copy of your sheets and I will try to create this application for you

    My Email: [email protected]

    PMP Certified

    [email protected]

    ☑️ 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å ✭✭✭✭✭✭
    edited 07/23/21

    Hi @Jessica Howell

    I hope you're well and safe!

    I'd recommend using the Sheet Summary section to either make the selection or at least replicate the selection so you can then use that as a reference in all formulas.

    An example of the formula would look something like this.

    =COUNTIFS({Range Year}, IFERROR(YEAR(@cell),0)=Year#, 
    {Range Week},IFERROR(WEEKNUMBER(@cell),0)=Week#, {Period Range} and so on.
    

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @Andrée Starå

    Thanks Andree! I am well, hoping you are too.

    I will give this a shot. Will this formula update the data when I change the circled criteria ? (manually) It wont always be the same every week

    Basically, when i change the year, period and week at the top, i need the fields below to update accordingly



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!