want all worksheet names as a list of names (text) on one workshee

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, anyone know how to make all of my worksheet names in an excel2000 file to
be listed in one spot (each name occupying one cel, all in a column). i want
to list each worksheet name, then a total next to it. I don't want to type
them individually because when i add a new worksheet i want this list to be
updated automatically.

i would imagine something like this should work (but doesn't)
=text (sheet1:sheet6)

is there a function that references the name of the worksheet?
i know you can have the worksheet name printed as a header....

thanx!
 
David McRitchie has several pages on this subject that should help you.

Start on these:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================






Hi, anyone know how to make all of my worksheet names in an excel2000 file
to
be listed in one spot (each name occupying one cel, all in a column). i
want
to list each worksheet name, then a total next to it. I don't want to type
them individually because when i add a new worksheet i want this list to be
updated automatically.

i would imagine something like this should work (but doesn't)
=text (sheet1:sheet6)

is there a function that references the name of the worksheet?
i know you can have the worksheet name printed as a header....

thanx!
 
You would need VBA to get the names and list them on a worksheet, but this
would not update when a new worksheet was added.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You're stuck with VBA macro for this.

Private Sub ListSheets()
'list of sheet names starting at A1 on activesheet
Dim sh As Worksheet
Dim rng As Range
Dim i As Integer

Set rng = Range("A1")
For Each sh In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = sh.Name
i = i + 1
Next sh
End Sub


Don't know what the "total next to it" would be.

Gord Dibben Excel MVP
 
Hi

You can use an UDF

---
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---

Now, when you want all your sheets to be listed started p.e. from cell A2,
then:
A2=IF(ISERROR(TABBYINDEX(ROW(A1))),"",TABBYINDEX(ROW(A1)))
and copy the formula down as much as you do need. Whenever you add new
sheets, they are listed in actual order so long as there is a formla ready.
 
Bob Phillips wrote...
You would need VBA to get the names and list them on a
worksheet, but this would not update when a new worksheet
was added.
Click to expand...

This could be done using a udf. See

http://www.google.com/[email protected]

Using the slst function in that archived message, you could select
likely range, like A5:A24 and enter the (nonarray) formula

=IF(ROW()-ROW($A$1)<ROWS(slst()),
INDEX(slst(),ROW()-ROW($A$1)+1),"")&T(RAND())

The &T(...) term at the end makes it volatile, so it'd update wheneve
worksheet names were changed. This could be dragged down int
additional rows as needed
 
I said added, not changed, change is easy with a UDF.

Also I note that it that archived message that the author, who has a name
very similar to yours, said '.. Best not to make this volatile...'<G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote...
I said added, not changed, change is easy with a UDF.
Click to expand...

Added isn't so hard either if the OP were to start off with 2 times a
many formulas as worksheets.
Also I note that it that archived message that the author, who
has a name very similar to yours, said '.. Best not to make this
volatile...'<G>
Click to expand...

Yup, don't make the udf itself volatile because it's simple to us
formula techniques to make it effectively volatile when that's what'
wanted. Adding 0*RAND() or appending T(RAND()) to any nonvolatile UD
makes it effectively volatile
 
Bob Phillips wrote...
I did, and you have to force a re-calculation.
Click to expand...

You're right. I had tested by deleting and renaming worksheets, which
do trigger recalc, but inserting new worksheets doesn't. For that
matter, neither NOW() nor RAND() recalc when a new worksheet is
inserted, but if I make the udf volatile, it does recalc when I insert
new worksheets. I guess there are different levels of volatility.
 
Bob Phillips wrote...

You're right. I had tested by deleting and renaming worksheets, which
do trigger recalc, but inserting new worksheets doesn't.
...
. I guess there are different levels of volatility.
Click to expand...

Precisely, which is what I said in my original response to the OP. Must
admit to being surprised that you weren't aware of that already H. I guess
that the creation of the worksheet does not make any change to the worksheet
cells/data, anything that would affect the volatility.

Anyway, it's a nice function and a nice formula to get a pseudo-ToC.
 
Bob Phillips wrote...
Precisely, which is what I said in my original response to the OP.
Click to expand...

Well, you did say it wouldn't update when new worksheets were added.
You didn't mention different levels of volatility.
Must admit to being surprised that you weren't aware of that
already H. I guess that the creation of the worksheet does not
make any change to the worksheet cells/data, anything that
would affect the volatility.
Click to expand...

Quite odd. With first worksheet Sheet1 and last worksheet Sheet4, I
appended T(COUNT(Sheet1:Sheet4!A1)) to the end of my formula, but it
didn't force the formulas to recalc. I even tried adding
T(COUNT(Sheet1:Sheet4!A1,RAND()), and it didn't work either. I even
tried T(ROWS(THREED(Sheet1:Sheet4!A1)+RAND()), and it didn't work (and
the expression inside T(...) doesn't recalc when worksheets are added).
So, no, I wasn't aware that inserting new worksheets didn't trigger
recalculation.

Still, easily addressed. In the workbook's ThisWorkbook class modure,
add a NewSheet event handler to force recalc.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.Calculate
End Sub
 
Back
Top