Indirect turns up with #ref error

I want to use `INDIRECT` in a formula like `=INDIRECT(CONCATENATE(A1,"+",A2),0)`;

where `A1 = 7` and `A2 = 8`.

It comes out with `#REF` error all the time, but if I use the `INDIRECT` formula like this: `=INDIRECT(B1)`; where `B1` content is `"J5"` and `J5` content is `11`, everything is fine and the indirect returns 11.

But as far as I know it should work in both cases. At least it works on my other computer.

Am I doing something wrong here?

-
I'm not sure what you're trying to achieve. `INDIRECT` takes only one parameter, and you used two parameters in your function, the first being `concatenate(a1,"+",a2)` and the second `0`. – Jerry Jan 29 '14 at 18:23

If you are simply trying to add `A1` to `A2` then `=SUM(A1, A2)` would make a lot more sense.

If you are trying to make it so you want to add two cells together, but you want to dynamically reference the cells based on some other criteria (which is the only reason why you would want `INDIRECT`) then you will need to do something like `=INDIRECT("A1") + INDIRECT("A2")` or `SUM(INDIRECT("A1"), INDIRECT("A2"))`. Although, written like that, it's really round-a-bout way of doing things.

Instead, if you set `B1` to be `"A1"` and `B2` to be `"A2"` then `=SUM(INDIRECT(B1), INDIRECT(B2))`... then you can change the values of `B1` and `B2` to whatever cells you would like to add together.

Edited to add: The second optional parameter is only necessary if you are going to use `R1C1` style of referencing a cell. When you concatenated `A1` with "+" with `A2` the result will be `A1+A2` which is not a reference to anything. If you wish to use `R1C1` style of referencing you can read more about how here

-

It looks like you're referencing your cell wrong. after the concatenate excel is taking `=INDIRECT("7+8",0)` which is not a valid cell address in `A1` or `R1C1` format.

I would suggest changing your CONCATENATE to `CONCATENATE("R" & A1 & "C" & A2)` which would make `INDIRECT("R7C8",0)` Which I am assuming is your goal.

I used `&` instead of `+` for clarity that I am not doing addition, but `+` would work fine too.

-