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?
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