# Need a different “INDIRECT” function in Excel

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?

-
Why does it matter that INDIRECT takes a string as input? – erikxiv Apr 24 '12 at 16:15
Have you tried `=AVERAGE(INDIRECT(\$X\$123))`? – Excellll Apr 24 '12 at 18:15

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.

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`.

-
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