Reference Dynamic Named Range dependent on cell value Excel-CodePudding

I am trying to dynamically reference a name range based on the contents of a cell.

For example, I have the following dynamic named ranges:

tCat1_Pass
tCat2_Pass
tCat3_Pass

Assuming we have the value 2 in Cell A1, I would like to reference tCat2 like the following

"tCat"&A1&"_Pass" which would = "tCat2_Pass"

INDIRECT doesn;t work with this, are there any other solutions, apart from writing a UDF or using CHOOSE?

CodePudding user response：

See below screenshot to use INDIRECT() function.

CodePudding user response：

I have solved this by writing a small UDF:

Public Function nm_return(cat_nbr As Integer, tbl_nm As String)
    
    nm_return = Range("tCat" & cat_nbr & tbl_nm)
    
End Function