Variable to return name of worksheet in vlookup formula

[twaccess]

I'm trying to write a formula which looks up the name of a worksheet to check if its in the index list.

=SHEET returns the number of the worksheet, but is there a way I can get the assigned name into this formula please ?

=vlookup("this worksheet name",index!A:A,1,false)

A more elegant way to do it would be a formula that says If this worksheet name is in the index, then "ok", else "not indexed". I can only think of vlookup though.

Thanks

T'dub

[FDibbins]

do you have a list of sheet names somewhere?

[twaccess]

Yes, I have an index page with the list of names and hyperlinks to all the sheets..

This formula is intended to provide a check as to whether the current sheet is included in this list or not.

I have a team who use this particular workbook of over 50 sheets. Each time they add another sheet, I want them to add the name to the index as well to help with navigation. If they don't then this formula will inform them hopefully.

[skywriter]

So in column A of my worksheet I have a list of sheet names. If I put the name of the current sheet in that list and use this formula I get ok, if I remove the name I get not indexed.

Please Login or Register  to view this content.

The A1 reference is just a cell on the sheet the formula is in, so that the formula returns the file name with the name of the sheet cell A1 is on. It doesn't matter what cell it is, nor does it matter if there is or isn't anything in that cell. If you reference a cell on another sheet, for instance Sheet2!A1 then it will return Sheet2 as the name to match. So just use A1 and it will always refer to the sheet the formula is on.

Where I use A:A you probably want to reference your Index sheet and column A like you did in your first post, if that is where you sheet name list is kept.

Good Luck.

[AliGW]

The attached macro-enabled workbook demonstrates a way of generating the sheet names automatically using a formula which was shared with me by someone here quite some time ago. Open it and look at the formula in Index | A1, which refers to a little macro called SheetNames. The formula can be dragged down as far as you like. Open the Name Manager on the Formula ribbon to add the simple macro.

[twaccess]

Thank you Skywriter and Ali for your responses... consider this thread solved...