CODEWITHSUNDEEP
functionexcelvba
John Snow
John Snow

Reputation: 5334

Function for getting a specific sheetname

I have a workbook with the following apperance:

enter image description here

This workbooks first sheet, "Resultat", calculates data from the other sheets in the workbook and presents them as the picture shows.

In order for the calculations to work correctly, the names shown in the marked red squares must be the same and corresponding, else the value will be ##### as you can see for entry number 2 (cell A5 works and have the same name as sheet number 2, but cell A8 do not work because it does not have the same name as sheet number 3).

My question is basicly, is it possible to use a function that will enter the name of a sheet in a specific cell. In this example, I would like the cell A5 automaticly fetch the name of the sheet with index 2, cell A8 be equal to sheet with index 3 and so on.

Right now im doing this manually but it would be a great help if this could be automated since I have alot of these workbooks and the names changes from time to time.

Upvotes: 3

Views: 3934

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Use this formula

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

The above will give you the name of the current sheet

If you change the reference of A1 to the relevant sheet then it will pick up that name.

Ex:

=MID(CELL("filename",Sheet1!A1), FIND("]", CELL("filename", Sheet1!A1))+ 1, 255)

This will give you Sheet1

Upvotes: 4

MikeD
MikeD

Reputation: 8941

Create a VBA module and enter the following code:

Public Function SheetNameByIndex(Index As Integer) As String
    SheetNameByIndex = ActiveWorkbook.Sheets(Index).Name
End Function

now, at any place in your workbook, you can do

=sheetnamebyindex(2)
=sheetnamebyindex(A1)

Upvotes: 1

Related Questions