Help with Indirect Function

[waldauer]

I am using Excel 2010 and I am trying to make a non-continuous list to be used in a drop down box. To my knowledge, the only way to do this is to create a name in the name manager and use the indirect function. I have two examples, the first one works, the second one does not. Is there any particular reason the second example does not work? Thank you in advance.

This example works:
=INDIRECT({"r12c2","r14c3","r15c2"},FALSE)

This example does not work:
=INDIRECT({"r12c"&(1+1),"r14c3","r15c2"},FALSE)

I have also tried using concatenate() in place of the ampersand (&) and it also does not work. Concatenating text within the indirect function is pretty normal, so I do not see why this does not work.

This is not the actual code I want to use, only a simplified example of the problem.

[oeldere]

Maybe you can this one.

=INDIRECT({"r12c"&"(1+1)","r14c3","r15c2"},FALSE)

[waldauer]

Did not work. The issue is with the text concatenation (the & symbol). Excel will not allow you to use that symbol in this context.

[Ace_XL]

Try..

="r12c"&TEXT(1+1,0) instead

[waldauer]

Still did not work. The issue is with the text concatenation (the & symbol). For example, the following line does not work either:

=INDIRECT({"r12c"&"2","r14c3","r15c2"},FALSE)

[Ace_XL]

In that case, how about...

=INDIRECT(ADDRESS(12,1+1))

[waldauer]

I tried:

=indirect({address(12,1),address(14,3),address(15,2)})

and it did not work. It gives me the generic "The formula you typed contains an error." message.

There does not to me appear to be any reason this should not work, unless it is simply outside of the capabilities of Excel.