One thought I had was instead of updating all the formulas manually to look at the appropriate sheet, put the sheet name in some cell, and then use the INDIRECT function in your VLOOKUP formula to look at that cell reference instead of hard-coding it right into the formula.
For example, if you put "EPO 2019" in cell A6, then your lookup formula could look like this:
Rich (BB code):
=VLOOKUP(A7,INDIRECT("'" & A6 &"'!A:E"),5,FALSE)
The advantage there is that instead of trying to pull the year out of the formula, you can just pull it out of A6 with a simple formula.
If that is not a viable solution, then I would probably create my own function in VBA to extract the year.
That User Defined Function, which needs to be inserted into a new VBA module in your workbook, may look like this:
VBA Code:
Function GetYear(cell As Range) As Variant
Dim frml As String
Dim st As Long
Dim ed As Long
' Only run on a single-cell range
If cell.CountLarge > 1 Then
GetYear = "Too many cells"
Else
' Get formula
frml = cell.Formula
' Get starting and ending spots of formula
st = InStr(frml, "'") + 1
ed = InStrRev(frml, "'") - 1
' Check to see if value found
If ed <= st Then
GetYear = "No sheet name found"
Else
GetYear = Right(Mid(frml, st, ed - st + 1), 4)
End If
End If
End Function
Then you would just use it like any other function, i.e.
if your formula was in cell B7, then you would use this formula to pull the year out: