Can you reference different sheets in a formula?


Hi all,

I've searched around and I can't find the answer - I have a colleague who had a formula in a sheet that referenced a number of other sheets (he's trying to count how many stores have reached a particular milestone in a geographical area). His formula worked for a while, but has stopped working. We're wondering if it's possible to reference multiple sheets in a single formula? I can't get it to work, but I'm not sure if it's me or if it's the system!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It can be depending on exactly what is being referenced and how. Are you able to provide the existing formula and more details as to what you mean when you say it is "not working"?

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    Hello @A&W Admin

    Yes you can use multiple references within a single function. (I'm using index because it's an easy example)

    First, we'll try to make sure all the references are still linked and not broken.

    Try putting the cursor where the reference is:

    Select "Edit Reference" and make sure each reference is still pointing to the right location.

  • A&W Admin
    A&W Admin ✭✭✭

    Thanks for the quick replies! This is the formula he's input:

    =IFERROR(AVG(COLLECT({BB Intake - Davaco PM & Logistics Fee}, {Construction Estimate Date - From D&C}, >Date@row, {Construction Estimate Date - From D&C}, <Date6, {Prototype Reference - From D&C}, "Boomerang 2315")), "")

    I believe he's gone through to confirm that no column headers have been changed. He said even when he goes in to try and set up a new formula it doesn't work

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You cannot reference multiple sheets within the same function. Each cross sheet reference within the COLLECT function has to be pointing at the same sheet and all of them must be of the same shape and size (all single column references for example).

    Are you able to describe the process and desired outcome in more detail?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!