User Defined Function In Array Formula

[Jaafar Tribak]

Hi,

I have this simple UDF that takes a worksheet index number and returns the worksheet name :

Function SheetFromIndex(index As Long) As String

    SheetFromIndex = ThisWorkbook.Worksheets(index).Name

End Function

The idea is to enter the following formula array in a range (say range A1:A3 ) and return the corresponding worksheet names in each cell ... obviously, that doesn't work and returns #Value error
{=SheetFromIndex(ROW())}

{=SheetFromIndex(ROW(A1:A3))} didn't work either.

Thanks for your help.

 

[MARK858]

It can't easily be used as a UDF. See post #13 for what Jon is suggesting.