# Office 365 >  >  INDIRECT with constant array not working as expected

## crwc7c

I have a named array constant: REF_TEXT={"A1","D3","G5"}. Basically its a list of cell references in text form. I also have a named formula: REF_LIST=INDIRECT(REF_TEXT). My expectation is that REF_LIST will return an array of references, such that =INDEX(REF_LIST,1) will return the value in cell A1, =INDEX(REF_LIST,2) the value in cell D3, and so on. Instead, =INDEX(REF_TEXT,1) is the only formula call that works, returning the correct value. =INDEX(REF_LIST,2) and all that follow result in a #REF! error.

I'm not exactly sure why this does not work. Is there a way to get INDIRECT to behave the way I want it to, or is this just not possible?

Thanks.

----------


## jeffreybrown

Not sure I completely understand what you are trying to do so could you mock up a workbook with sample of what you expect!

----------


## crwc7c

Well, my overall goal is to create named formula's that can take input parameters, which I have successfully accomplished. I am trying to improve upon my implementation by using arrays. It all seems to be working the way it should except for what I described above. I can't exactly mock up the workbook because there isn't really anything in the cells; it's all in the Name Manager. The basic idea behind allowing for named functions with input parameters is using the N() or T() functions to house the input parameters and collecting them within the named formula with FORMULATEXT(). That is where I get an array of references in text form. As stated above, I would like to feed that array into INDIRECT() to get an array of usable references to implement in the formula.

----------


## Tony Valko

If you do it like this...

=INDIRECT(INDEX(REF_TEXT,2))

It'll work.

That formula will return the contents of cell D3.

----------


## crwc7c

Yes, I do understand how INDIRECT() normally works. The result I want is an array of references. INDIRECT() is less of an issue as it was before. Though I still would like to understand how to have it return an array of references, figuring this out won't improve the functionality of my workbook as much as before. I have attached my workbook. As I stated above, my overall goal is to create named functions with input parameters. I have accomplished this, but it requires too many named formulas to be practical. Please look over the named functions and let me know if there is anything I can do to make this easier to implement.
Thank you for your time.

----------


## Tony Valko

I looked at your file but I'm totally confused.  :Confused:

----------


## crwc7c

Haha, I was afraid that would happen, unfortunately. I have added comments to each of my named functions that explain what they do. I have also included an overall summary of what is going on. I hope that this will help make it less confusing, but I'm afraid that it won't.
Thanks again.

----------


## Tony Valko

Sorry, this is very difficult to follow without knowing the end goal. I did read your note on the Summary page.

Just looking at 1 formula and it may use several defined names. Then you start looking at those defined names and they also refer to other defined names.

I wonder if you're trying to reinvent the wheel with this?

This is the data in your file:

Data Range 

*A*
*B*
*C*
*D*
*E*
*F*

*5*
------
------
------
------
------
------

*6*

4

works
5


*7*


6




*8*










What are you wanting to do with it?

----------


## crwc7c

Well, here's the issue. I don't actually have a specific task or problem in mind. My overall goal is to be able to create named formulas that take in inputs, but do so without using macros. Again, what I've currently got does this and does it pretty well. The implementation is just a bit much to be practical. I have had times in the past where being able to create named formulas that take inputs would have been very convenient, but I'm currently not trying to accomplish anything specific. The workbook you are looking at is purely academic, basically just showing examples of how my current implementation works. The data you are referring to is meaningless. It's just there to display how the inputs can be cell references. I'm still not sure how to explain how it works. What I've implemented allows you to do the following.

In a cell: =N([NPAR1]&[INPAR2]...)+[formula using NPAR1, NPAR2... as parameters]

The [formula using NPAR1, NPAR2...]  can be a named formula or typed directly in the cell. This actually works very well and is convenient. Once it is set up, it can be used in any number of formulas. The only down side is that it takes a bunch of confusing named formulas to set it up. If you can think of a better way to implement named formulas with inputs and no macros, or if you can find any way to clean up all of the named formulas that go into this implementation, that is what I am after. If its all too confusing to be worth looking through, no worries. I just thought I might get a fresh pair of eyes to look it over. 

Thanks for bearing with me.

----------


## Tony Valko

What advantage is there in doing this versus using the built-in functions?

----------


## crwc7c

There really isn't any if there exists a built-in function that does exactly what you want. However, there is a reason why people create UDF. The built-in functions can't always satisfy what you need. What I've done just makes it possible to create UDFs without using macros. Of course I could be completely off base with this. I'm not sure what you mean by "using built-in functions" as an alternative. Of course you will use built-in functions wherever and whenever they apply, which includes using them inside of the UDFs.

Again, I may not have completely understood what you meant, so please fill me in if I'm missing something. 

Thanks again.

----------

