Can we make reference to Named Ranges Dynamic?

[SanjayGMusafir]

Hi Experts,

Can we make reference to Named Ranges Dynamic?

for eg.
I have Named ranges
SD23
SD22

where number part represents last 2 Digits of an year.

Now I want to use reference to these named ranges to pull their values.

Using

=SD23

is working absolutely fine.

But thought crossed my mind if I can reference them dynamically, it will make my life much easier.

To Explain

I have Year in cell A1
And if I can create reference in B1 like

=SD&Right(A1,2)

I tried the above idea but it's not working gives #Name? error...

Then I tried

="SD"&Right(A1,2)

This obviously returns Text "SD23" instead of referencing

Can anyone help solving this issue.

Thanks is advance

 

[SanjayGMusafir]

Solved using -

=Indirect("SD"&Right(A1,2))

Got my solution on this link -

https://stackoverflow.com/questions/74394141/how-to-dynamically-call-a-named-range-in-an-excel-formula