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?

1 Answer 1

12

You can try using 3D cell references.

  1. Select the cell in which you'd like to place the reference. Type =.
  2. While holding Shift, click the first sheet and then click the last sheet.
  3. 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)

Creating a Name for your 3D Reference

This will make it easier for you to reference the columns.

  1. Go to Formulas > Defined Names
  2. Type your preferred name. In the example below, I used "myrange".
  3. Delete the contents of the Refers to box and type =.
  4. While holding Shift, click the first sheet that you want to reference, and then click the last sheet.
  5. Select the column that you want to reference. In your case, column J. You'll get something like this:

enter image description here

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

5
  • 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
    Sep 5, 2011 at 9:40
  • @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
    Sep 5, 2011 at 10:20
  • 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
    Sep 6, 2011 at 14:30
  • Conditional formatting: Make the complex formula evaluate (e.g. =true) into a "hidden" (intermediate) cell, then do the formatting based on that cell.
    – Hannu
    Jul 2, 2015 at 20:57
  • @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
    Jul 2, 2015 at 21:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .