Cell references in an Excel array formula

[Schizoid Man]

Hello all,

It's possible for me to enter in an array formula directly as:
={100,200}, or even
=TRANSPOSE({100,200})

However is it possible to enter in an array formula of the type = {<cell
ref>, <cell ref>}? Each time I try to do this in Excel 2003 or Excel 2007 I
get the following error message: "The formula you typed contains an error."

Is it possible to use INDIRECT() or some other similar reference function to
accomplish this?

I am using a custom Excel add-in that has a function that needs an array as
one of its parameters. Due to the layout of the sheet, I'm a bit restricted
in terms of the layout.

Regards,
Schiz

 

[T. Valko]

If the cells are a contiguous range then you should be able to just use the
range reference provided your function will accept it:

A1:A5

If the range is not contiguous you can try something like this:

INDIRECT({"A1","B6","C3"})

 

[Shane Devenshire]

Hi,

If you type =A1:A10 in a cell that is an array or you can type it and then
press Shift+Ctrl+Enter then it will display on the formula bar as {=A1:A10}

Similarly if you type =A1,D1,G1 and press enter this is technically an array
or you can hold down the Shift+Ctrl+Enter key as you enter it to get
{=A1,D1,G1}

So you may be able to imbed these in your custom function, I really don't
know.

 

[Schizoid Man]

If the cells are a contiguous range then you should be able to just use
the range reference provided your function will accept it:

A1:A5

If the range is not contiguous you can try something like this:

INDIRECT({"A1","B6","C3"})

Thanks, Biff. This is perfect, I was trying
{INDIRECT("A1"),INDIRECT("B2"),INDIRECT("C3")}, so it was just bad syntax.
Super stuff! Thanks a lot.

 

[T. Valko]

You're welcome. Thanks for the feedback!