6
In Excel, =$J:$J
references an entire column on the current sheet while ='Sheet2'!$J:$J
references that entire column on Sheet2
explicitly.
Is there a way to reference column J
on all sheets without manually typing all the sheet names?
6
In Excel, =$J:$J
references an entire column on the current sheet while ='Sheet2'!$J:$J
references that entire column on Sheet2
explicitly.
Is there a way to reference column J
on all sheets without manually typing all the sheet names?
11
You can try using 3D cell references.
=
.Select the column that you want to reference. You'll end up with something like this:
='Sheet1:Sheet3'!J:J
For example, if you want to sum the contents of column J on all three sheets you can use the formula:
=SUM('Sheet1:Sheet3'!J:J)
This will make it easier for you to reference the columns.
=
.To sum the contents of column J in Sheet1
through Sheet3
, you just have to use this formula:
=SUM(myrange)
Not all Excel formulas can accept 3D references. Here are the ones that do: SUM
, AVERAGE
, AVERAGEA
, COUNT
, COUNTA
, MAX
, MAXA
, MIN
, MINA
, PRODUCT
, STDEV
, STDEVA
, STDEVP
, STDEVPA
, VAR
, VARA
, VARP
, VARPA
Conditional formatting: Make the complex formula evaluate (e.g. =true) into a "hidden" (intermediate) cell, then do the formatting based on that cell. – Hannu – 2015-07-02T20:57:44.493
@Hannu You can even put the formula into a named range. Calling that from th CF will do the same without wasting a cell. – wbeard52 – 2015-07-02T21:55:09.750
This answers the question (so +1) but it might be wise to caution about summing many whole columns at once. At least put a sensible range in such as 'Sheet1:Sheet3'!$J$1:$J$9999 or a suitably high number without including 1 million rows unecessarily – AdamV – 2011-09-05T09:40:48.713
@AdamV I agree with you. Though the OP stated he wanted to call on the entire column, it's good practice to avoid doing so. – Ellesa – 2011-09-05T10:20:31.730
This answers my question. However, I am using the Conditional Formatting Rules Manager and it won't accept a 3D range and therefore does not solve my problem. But that's not your fault, so answer accepted. – Benjotron – 2011-09-06T14:30:32.077