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.

I have multiple columns and at the end of each one I have a SUM and MEAN lines. Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this for each formula is a tedious task.

In my previous question I found out how to do it for a single column. I would place the A1:A15 string in a separate cell, then reference it using INDIRECT. This way I can alter a single cell and update every formula for that column.

The problem is that I have multiple columns, with this approach I would have to update the B1:B15 cell for each column. How to simplify this update process?

share|improve this question
    
I can write an answer to this if I figure out how to superuser.com/questions/316239/… –  Jader Dias Jul 28 '11 at 14:33
add comment

1 Answer 1

up vote 3 down vote accepted

You can use the OFFSET function to shift an entire range by a certain amount. For example, if you had the text ="A1:A15" in cell C1, you could get the range B1:B15 by using the following formula:

=OFFSET(INDIRECT(C1), 0, 1)

For your reference, the function is defined in Excel as =OFFSET(reference, rows, columns, [height], [width]). To retrain the width/height of the original range, do not specify the height or width arguments. Also, note that the rows and columns arguments can be positive or negative (so you can shift both up/down and left/right).


Just another note, the OFFSET function works with entire ranges, so if in the previous example you entered the text ="A1:A12,A15" the returned range after using OFFSET to shift it right one column would be B2:B12,B15 as you would expect.

share|improve this answer
    
I used OFFSET(INDIRECT(C1), 0, COLS(A16)-1) –  Jader Dias Jul 28 '11 at 16:50
add comment

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.