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.

Attachments

• 67.4 KB Views: 5

Excel Wizard

Member
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

Attachments

• 68.8 KB Views: 5

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

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