formula to find Sheet name in different worksheet

[SamS]

Ivan (with greatful thanks) posted this formula recently which places the worksheet name into a cell which I have used more and more of late.

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

I have 2 variations I need to use.
1) I want the cell to reference a different worksheet (which is a department name), have tried a number of alternatives but am having no luck.
2) I then require another cell which is a formula (for use in a graph) to pick up this same reference ie replace the Network Program Delivery in ="Network Program Delivery'!$K$1 with the worksheet reference.

I know it seems to be a heavy handed way of doing things (and there probably is some simple way of doing it) but the way department names change regularily around here I only want to make one change and that is to the worksheet name.

Will be eternally gratefull if someone can supply the info
Edit: too early in the morning here-also off the coffee which probably explains it. Prob no2 is not a prob any more, will just rely on the formula automatically changing with the worksheet name change

[ This Message was edited by: SamS on 2002-05-21 15:49 ]

[Jay Petrulis]

Hi,

You would likely need a user-defined function (UDF) to do this, although I can't tell exactly what you are requesting.

Function test()

    test = Application.Caller.Worksheet.Next.Name

End Function



Function test2()

    test2 = ActiveSheet.Next.Name

End Function

These two functions should work for you, after modification. I think the top is a bit more stable. These just get the next sheet after the sheet where you place the formula.

Bye,
Jay

[SamS]

Thanks Jay, will play around with this and see what comes up.