Formula to put in a Name Range that retrieves the "Refers to" from a formula in a cell

[jbesclapez]

Hello Mr Excel!

Hope you are all doing fine!
I am contacting you because I have a formula in J9 that displays as a result:

Accounts!$A$5:$E$500

And I would like to use the value displayed in the cell J9 to define a named range. So I am trying to put in a name range :
=INDIRECT(Validation!$J$9)
But then it seems that the name ranged is not displayed in the drop down list top left corner.

It seems that it is not working. How can I solve that?

Thanks

 

[Fluff]

It should work, but it will not appear in the name box, as it is not a set range.

 

[jbesclapez]

It should work, but it will not appear in the name box, as it is not a set range.

Thanks for confirming Fluff! After your confirmed it should work, I perserved and saw that I needed the = signe also in the formula. So it shoudl be (See below)
Thanks for putting me back on the right track!

=Accounts!$A$5:$E$500

 

[Fluff]

Glad to help & thanks for the feedback.