0

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?

1
  • 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, 2014 at 18:23

2 Answers 2

2

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

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .