Hi all,

Is there a way to define a range as my "TimePeriod", let's say $AL$5:$AL$1500 without specifying the sheet? I would need to use the exact same range on different sheets, so I want to specify which sheet it should refer to within the formula I'm using.

e.g

original formula:

=IF(F37=0,"-",IFERROR(INDEX('Location Plan'!$G$5:$G$1500,MATCH(LARGE(IF((1)*(1)*((1)),'Location Plan'!$BG$5:$BG$1500),A37),'Location Plan'!$BG$5:$BG$1500,0)),"-"))
adjusted formula with defined range:

=IF(F37=0,"-",IFERROR(INDEX('Location Plan'!$G$5:$G$1500,MATCH(LARGE(IF((1)*(1)*((1)),TimePeriod),A37),TimePeriod)),"-"))
my range is defined in the following VBA code which is linked to my form control box where one can select the time period:

   Sub TimePeriodComboBox_Change()
    
    'Dim rng As Range
    
    Select Case Worksheets("Control").Range("L3")
        Case Is = "FY 2016"
            Select Case Worksheets("Control").Range("N3")
                Case Is = "MONTH"
                    ActiveWorkbook.Names.Add "TimePeriod", Worksheets("Location plan").Range("$AL$5:$AL$1500")
                    'since LY doesnt exist..points to same year
                    ActiveWorkbook.Names.Add "LY", Worksheets("Location plan").Range("$AL$5:$AL$1500")
                    
                Case Else
                    ActiveWorkbook.Names.Add "TimePeriod", Worksheets("Location plan").Range("$U$5:$U$1500").Offset(0, Worksheets("Control").Range("o10") - 1)
                    
                    'since LY doesnt exist..points to same month
                    ActiveWorkbook.Names.Add "LY", Worksheets("Location plan").Range("$U$5:$U$1500").Offset(0, Worksheets("Control").Range("o10") - 1)
            End Select
            
        'change the offset for different FY...i left code in there that directs to 2016 to stop errors pumping out
        
        Case Is = "FY 2017"
            
            Select Case Worksheets("Control").Range("N3")
                Case Is = "MONTH"
                    ActiveWorkbook.Names.Add "TimePeriod", Worksheets("Location plan").Range("$BG$5:$BG$1500")
                    ActiveWorkbook.Names.Add "LY", Worksheets("Location plan").Range("$AL$5:$AL$1500")
                Case Else
                    ActiveWorkbook.Names.Add "TimePeriod", Worksheets("Location plan").Range("$AP$5:$AP$1500").Offset(0, Worksheets("Control").Range("o10") - 1)
                    ActiveWorkbook.Names.Add "LY", Worksheets("Location plan").Range("$U$5:$U$1500").Offset(0, Worksheets("Control").Range("o10") - 1)
            End Select
            
        Case Else
            MsgBox "Fix up the code for everything else"
            Exit Sub
    End Select
   
End Sub
As you can see, in the above code, the range is still defined by referring to a particular sheet (Location Plan) though, I want it to only define the range and then specify the sheet it should refer to in the formula I mentioned at the top. 

ActiveWorkbook.Names.Add "TimePeriod", Range("$AL$5:$AL$1500")
ActiveWorkbook.Names.Add "LY", Range("$AL$5:$AL$1500")
=IFERROR(LARGE(IF((1)*(1)*((1)),'Location Plan'!TimePeriod),B38),"-")
Something like the above...

Is that even feasible? (The above doesn't work...)