Variable Tab References by location

[rmc5446]

Does anyone know the way to reference the farthest right tab in a workbook?

The formula would be on a summary tab, location of summary is the far left in the workbook, and essentially be a =vlookup(ref,"furthest right tab"&A:B,2,0)

Thank you for your help

Rob

 

[Jonmo1]

Welcome to the board.

To my knowledge there is no built in function to refer to sheets by their index# or left/right position.

You would need to use a VBA user defined function.

Try this
Right click your sheet's tab, select View Code
Click Insert - Module
Paste the following

Public Function RightSheet() As String
Application.Volatile
RightSheet = "'" & Sheets(Sheets.Count).Name & "'"
End Function

Now you can use this with Indirect
=vlookup(ref,INDIRECT(RightSheet()&"!A:B"),2,0)


Hope that helps.

 

[rmc5446]

Thanks! That works .

 

[Jonmo1]

Glad to help, thanks for the feedback.