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 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?

share|improve this question
    
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 at 18:23

2 Answers 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

share|improve this answer

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.

share|improve this answer

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.