How to use text for a cell as part of a formula

Hi,   Can someone help with an excel formula?   I'm trying to add a dynamic text within a formula so the named range in my formula changes, I have a list of named ranges in my data table, and on ...