In a Module, paste following UDF:

Public Function lastSheetName() As String
Application.Volatile
lastSheetName = Worksheets(Worksheets.Count).Name
End Function

Then you can use formula:
=INDIRECT(lastsheetname()&"!A1")+INDIRECT(lastsheetname()&"!B1")

HTH
--
AP

"L.K." <lado@internews.ge> a écrit dans le message de
news:uFHtaf%23UGHA.4348@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have workbook with the spreadsheets "Input", "Year 1", "Year 2", "Year
3",
> ....
> Workbook has different number of spreadsheets. Last spreadsheet name is
> "Year 5" or "Year 6" or "Year 7" ... and it depends how many spreadsheets
> are in workbook.
>
> On the first sheet "Input" I have formula which refers to the last
> spreadsheets cells (sometimes it is "Year 7" and sometimes "Year 9" ...
and
> so on).
> Let this formula is:
> ='Year 7'!A1+'Year 7'!B1
>
> How I can write universal formula (or macro) there instead concrete
> spreadsheets names will be reference to the last spreadsheet name
(sometimes
> it is "Year 6", sometimes "Year 9" and so on) of workbook.
>
> Thank you in advance.
> Best wishes,
> Lado
>
>