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 a spreadsheet that has about 16 equations that use ranges.

I want to be able to copy the data and equations for multiple runs of the data. With different runs of the data, the ranges are different.

Rather than edit the ranges in 16 equations, I would like to put the range in a single cell that is used by all the equations. Then I can just edit the range once.

Named Ranges don't really help because when I copy the set of equations, the named ranges are copied too, and I have to edit all 16 of them to different ranges, as well as create new named ranges for each instance of the data.

The INDIRECT function doesn't help because it takes a string as input, not a range.

Ideally, I could put a range in cell X123, and edit it using the normal range specification techniques. In my equations I would just put =Average(!X123), if ! was the magical indirect operator, and the equation would provide the average of the cells in the range contained in X123.

Is there a good way to accomplish this?

share|improve this question
1  
Why does it matter that INDIRECT takes a string as input? –  erikxiv Apr 24 '12 at 16:15
1  
Have you tried =AVERAGE(INDIRECT($X$123))? –  Excellll Apr 24 '12 at 18:15

1 Answer 1

up vote 2 down vote accepted

The INDIRECT function doesn't help because it takes a string as input, not a range. Ideally, I could put a range in cell X123, ...

I think you're confused about what you're putting in X123. Here you will enter, as text, the address of a range. The content of the cell is a string, not a range.

You will pass this string to the INDIRECT() function by way of a range reference. =INDIRECT(X123) will return the contents of X123.

EDIT:

Your comment made me realize you probably wanted something different. I think what you are looking for is a way to use an indirect cell reference as a relative reference, i.e. you want to be able to use the indirect reference to refer to one thing when used in A1, but to refer to something else (one column over) when used in B1. You can do this with a combination of INDIRECT, OFFSET, ROW, and COLUMN.

For example, A2:A11 contains a set of data, as does B2:B11. In D1 you have the address "A2:A11" stored as text.

sample data screen capture

So if you want to find the average of each data set using the indirect reference in D1, you can use the following fill-able formula (in D6 in this example):

=AVERAGE(OFFSET(INDIRECT($D$1),ROW()-ROW($D$6),COLUMN()-COLUMN($D$6)))

Filling this formula one cell to the right (E6) will give you the average of B2:B11. The key to this formula is that you anchor it to a fixed point of reference, $D$6 in this example. In D6, the indirect reference will not be offset at all. The indirect reference will be offset by the same number of cells (up/down and left/right) you are offset from D6.

share|improve this answer
    
Yes, this is correct. The examples make it look like a string has to be "assembled", but in fact it will accept a simple range in a cell. The only downside is that the range in the cell is not recognized by Excel as a range, so it doesn't get updated when copied to a different part of the worksheet. –  tim11g Apr 25 '12 at 14:55
    
Your comment made me think that I didn't really give you what you were looking for. See my edit for what is hopefully a more helpful solution. –  Excellll Apr 25 '12 at 15:31

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.