• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect Function Help Required

anishms

Member
Hi All,

In the below screen shot, I'm referring to a named range called "Function" but the formula gives #REF! error.
Somebody please provide a solution

71906
Thanks in advance
 

Attachments

INDIRECT can only refer to normal range name eg ='Clinics Verticlal'!$A$2:$A$99, not a formula

If you need dynamic range, better use Table and avoid volatile function like OFFSET and INDIRECT

=UNIQUE(INDEX(Table1,,MATCH(D3,Table1[#Headers],)))
 

Attachments

anishms

Member
T
INDIRECT can only refer to normal range name eg ='Clinics Verticlal'!$A$2:$A$99, not a formula

If you need dynamic range, better use Table and avoid volatile function like OFFSET and INDIRECT

=UNIQUE(INDEX(Table1,,MATCH(D3,Table1[#Headers],)))
Thanks for the clarification, I didn't use table because I may need to marge some of the cells in the data
 

Peter Bartholomew

Well-Known Member
I fully concur with @Excel Wizard . You have sacrificed too much for the dubious option of merging a few cells, starting with needing to employ computationally expensive and largely obsolete methods of generating dynamic ranges.

I would tend to use XLOOKUP rather than the INDEX/XMATCH combination to return a field from a table but there is not much to choose
= UNIQUE( XLOOKUP(D3, Table1[#Headers], Table1) )

If you really do wish to persist with your present strategy, the Name "function" can be calculated using the old EVALUATE() function. The catch is that EVALUATE does not work on the grid; it must be called from a formula within a defined name.
 

anishms

Member
I fully concur with @Excel Wizard . You have sacrificed too much for the dubious option of merging a few cells, starting with needing to employ computationally expensive and largely obsolete methods of generating dynamic ranges.

I would tend to use XLOOKUP rather than the INDEX/XMATCH combination to return a field from a table but there is not much to choose
= UNIQUE( XLOOKUP(D3, Table1[#Headers], Table1) )

If you really do wish to persist with your present strategy, the Name "function" can be calculated using the old EVALUATE() function. The catch is that EVALUATE does not work on the grid; it must be called from a formula within a defined name.
Thanks for your suggestions, I will make a version with table and prefer to use that if no merging is used
 
Top