Is it Possible ? Auto Updating Names of Named Ranges

[RAF43]

Hello Everyone,

Is it possible to have the "name" of a named range change automatically to match the text of a specific cell as it changes ?

For example: Assume there is a named range for cell A1 that is Team_A. That also happens to match the text of cell A1. If the text in cell A1 happens to change to Team B, is it possible to have the named range also change automatically to Team_B and all referenced formulas change accordingly ?

The reason: This is probably not a best practices approach but it works. I'm using the drop down named range selection box to quickly find and locate the cursor to a given cell by simply selecting a named range in a protected worksheet. If the Named range matches the name of a specific cell, then that cell text changes, I'd like the name range to update and reflect the new text.

You guys are the best - Many thanks !
Randy

 

[RAF43]

Works Perfectly - Thank you again Sir. Amazing help !
Randy

 

[RAF43]

What do you want to happen to the formulas that use that named range.

If you have "TeamA" in A1 and a named range TeamA that refers to A1 and a formula =OFFSET(TeamA, 1,0)

What do you want the formula to be after the user enters "TeamB" into A1?
Also, after the entry, do you want there to still be a named range called TeamA?

The VBA Rick suggested was exactly what I was looking for. Thank you Mike for raising that concern, but it is not an issue. You guys are the best ... truly.