Indirect Named Range Inception

[Celticfc]

I have a named cell/range called Orders in A1, which would store the name of my other named ranges (i.e. when I click “Fruits” tab, the word “Fruits”gets pasted to my Orders named cell through VBA.

What I would like to do is to use Indirect within my formulas, which would reference the named range within the Orders named cell?
Is this possible at all?

Many thanks in advance.

 

[Fazza]

Sounds OK. Maybe with VBA like this in the workbook's module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("Orders").Value2 = Sh.Name
End Sub

 

[RoryA]

Do you mean just INDIRECT(orders)?