Can you reference different sheets in a formula?

A&W Admin

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

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

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

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

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?