reading the sheetnumber for use in cel data

[Guurt]

Hi All (especially Roman),

Continuing on the use of Sheetnumbers, is it possible to read out the
sheetnumber, to be used in cel data?

For instance on Sheet10 I want cel A1 to read the Sheetnumber and be 10,
too.

on Sheet335 I want cel A1 to be 335

etc.

???????

Thanx!

Guurt

 

[Roman]

Hey Guurt
unfortunately it is not possible to read anything from sheet name
without using VBA programming or user defined functions.

Here is an user defined function, that reads numbers from sheetnames.
It returns -1 if the sheetname has no number on the end

Function sheetnumber(anyrange As Range) As Integer
Dim sheetname As String
sheetname = anyrange.Parent.Name
For x = 2 To Len(sheetname)
If Not IsNumeric(Right(sheetname, x)) Then
If IsNumeric(Right(sheetname, x - 1)) Then
sheetnumber = evaluate(Right(sheetname, x - 1))
Exit Function
End If
End If
Next x
sheetnumber = -1
End Function

Press Alt+F11, insert a modul under your project and paste the function
into that module.
Then you'll find a new function in Insert / Function / Userdefined
(This function will be available in the workbook you paste it in.)