Formula with the last spreadsheet of workbook

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

Go into the VB Editor (Alt-F11) and then choose Insert, Module to place a new
module in your workbook. Paste the following into it and then you can use
=LastSheet(A1)+LastSheet(A2) etc.

Function LastSheet(cellref) As String
LastSheet = Worksheets(Worksheets.Count).Range(cellref.Address)
End Function


"L.K." wrote:

> 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
>
>
>

Martin,

Thank you so much. It's great.

Lado


"Martin" <Martin@discussions.microsoft.com> wrote in message
news:FA1DFE96-B2DC-4DA2-8400-C6740438A4CA@microsoft.com...
> Go into the VB Editor (Alt-F11) and then choose Insert, Module to place a
> new
> module in your workbook. Paste the following into it and then you can use
> =LastSheet(A1)+LastSheet(A2) etc.
>
> Function LastSheet(cellref) As String
> LastSheet = Worksheets(Worksheets.Count).Range(cellref.Address)
> End Function
>
>
> "L.K." wrote:
>
>> 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
>>
>>
>>

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
>
>

No problem Lado but looking at Ardus' reply reminded me that it can get
annoying that this function doesn't recalculate without Application.Volatile
(also it shouldn't strictly have As String at the end!). Could you change it
to:

Function LastSheet(cellref)
Application.Volatile
LastSheet = Worksheets(Worksheets.Count).Range(cellref.Address)
End Function


"L.K." wrote:

> Martin,
>
> Thank you so much. It's great.
>
> Lado
>
>
> "Martin" <Martin@discussions.microsoft.com> wrote in message
> news:FA1DFE96-B2DC-4DA2-8400-C6740438A4CA@microsoft.com...
> > Go into the VB Editor (Alt-F11) and then choose Insert, Module to place a
> > new
> > module in your workbook. Paste the following into it and then you can use
> > =LastSheet(A1)+LastSheet(A2) etc.
> >
> > Function LastSheet(cellref) As String
> > LastSheet = Worksheets(Worksheets.Count).Range(cellref.Address)
> > End Function
> >
> >
> > "L.K." wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>