Formula to return Sheet Number

[HikerBrian]

I want a formula to return the sheet number, and I've done some searching and found the following two threads:

http://www.mrexcel.com/forum/showthread.php?t=364245

http://www.mrexcel.com/forum/showthread.php?t=362728&highlight=sheet+formula

The first thread says there isn't a formula, but that was two years ago, I was wondering if anyone here today knows of a way to make it work?

 

[T. Valko]

OK, then we can make the function volatile like this:

=GET.DOCUMENT(87)&T(NOW())

Seems to take care of it.

Let's change that to:

=GET.DOCUMENT(87)+(NOW()*0)

The previous version returned a TEXT number. This new version returns a numeric number.

 

[Rick Rothstein]

Let's change that to:

=GET.DOCUMENT(87)+(NOW()*0)

The previous version returned a TEXT number. This new version returns a numeric number.

Okay, that is better. Very good! By the way, you can remove the surrounding parentheses and it will still work...

=GET.DOCUMENT(87)+NOW()*0

 

[T. Valko]

Is there a similar function I can use (VBA is ok) to get the total number of sheets in a workbook?

Thanks!

Again, using a macro function...

Create the defined formula name:

• SheetNames
• Refers to: =GET.WORKBOOK(1)&T(NOW())

Then, on the worksheet use this formula:

=COUNTA(SheetNames)

 

[Legacy 167133]

Nice solution T. Valko ! But if I change a sheets visibility to "VeryHidden", how do I exclude that or other "VeryHidden" sheets to be counted?

 

[T. Valko]

Nice solution T. Valko ! But if I change a sheets visibility to "VeryHidden", how do I exclude that or other "VeryHidden" sheets to be counted?

That I don't know and I was thinking about that same scenario.

You would probably have to use a UDF.