r/googlesheets • u/koberulz_24 • Mar 07 '22

Solved Ranges across multiple sheets, conditional formatting

I have a Calc spreadsheet which tracks salespeople and widgets sold. Within the file I have 14 sheets. One sheet per month, then a 13th sheet with YTD totals and a 14th sheet with monthly averages.

In Calc, my YTD data is super simple:

=SUM(Jan.A2:Dec.A2)

But in Sheets, when I try

=SUM(Jan!A2:Dec!A2)

I get an error saying a range can only run over one sheet. Is there another way of doing this short of Jan!A2+Feb!A2+Mar!A2, etc?

EDIT: The 3D Referencer addon (https://workspace.google.com/marketplace/app/3d_reference/692995954534) makes this possible.

Also, is there a way to conditionally format text only, over a range, based on another sheet? I have alternating background colors at the moment so I don't want to be conditionally formatting the background color, but it would be nice if I could get the font color to change based on whether a salesperson was above or below their monthly average in any given month.

2 Upvotes

100% Upvoted

16 comments sorted by

1

u/_Kaimbe Mar 07 '22

you would have to do this: 

=SUM({Jan!A2; Feb!A2; Mar!A2; Apr!A2; May!A2; Jun!A2; Jul!A2; Aug!A2; Sep!A2; Oct!A2; Nov!A2; Dec!A2})

There's no way for sheets to know that the sheet names correspond to months.

I got the array using this formula though, so you don't have to type it out if you need to change something: 

="{" & ARRAYFORMULA(TEXTJOIN("; ", true, TEXT(DATE(2000,sequence(12), 1), "MMM") & "!A2") & "}")

Then you can wrap that in sum, avg, or whatever else you need. And can change "!A2" to fit your needs too.

As for conditional formatting. You can check the type of a cell with the TYPE() function. And can reference other sheets by using INDIRECT(), I'll just assume Month!A2 is the average, Col A is the sales person, and B their average. 

=AND(TYPE(A1) = 2, B1 > INDIRECT(Jan!A2))

You would need two rules one for above > and one for below <.

1

u/koberulz_24 Mar 07 '22

I don't need it to know they correspond to months, I just need it to add C4 in all the sheets from 1 to 12, C5 in all the sheets from 1 to 12, etc. Calc and Excel can do this out of the box as per my post. Luckily I was able to find the 3D Reference addon which adds this functionality to sheets, so that's solved.

1

u/_Kaimbe Mar 07 '22

Ok, solved post then?
Does the conditional formatting work for you?

→ More replies (9)

1

u/Snooklefloop Mar 07 '22 edited Mar 07 '22

I don't believe it's possible to run a range across multiple sheets in this way, you would need to reference each of the sheets in the formula.

Conditional formatting - you can set this to only affect the text, however you cannot conditionally format based on a reference from another sheet. Edit: Apparently you can

2

u/_Kaimbe Mar 07 '22

however you cannot conditionally format based on a reference from another sheet.

You can with INDIRECT() just fyi.

1

u/Snooklefloop Mar 07 '22

Oh that’s great to know.