Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel, =$J:$J references a column, and ='Sheet2'!$J:$J will reference that column on Sheet2 explicitly.

Is there a way to reference column J and all sheets without manually typing all the sheet names?

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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

share|improve this answer
    
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 '11 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 '11 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 '11 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.