table of contents
Posted by odoy on December 14, 2001 10:22 AM
Hi,
Does anybody knows of a way to create a table of contents in excel? Just like Word does.
I am trying to make a table of contents with each worksheet page number on it. I can do it manually but it would be great to have it automatic, so I don't have to update it everytime I want to print. Thanks.
Odoy
Posted by Joe Was on December 14, 2001 3:12 PM
This is a copy of j-walk's tip #62
Copy this fuction to a module, using the VisualBasic editor under macro:
Function CellType(c)
' Returns the cell type of the upper left
' cell in a range
Application.Volatile
Set c = c.Range("A1")
Select Case True
Case IsEmpty(c): CellType = "Blank"
Case Application.IsText(c): CellType = "Text"
Case Application.IsLogical(c): CellType = "Logical"
Case Application.IsErr(c): CellType = "Error"
Case IsDate(c): CellType = "Date"
Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
Case IsNumeric(c): CellType = "Value"
End Select
End Function
Then off to the right of the working part of the sheet you want to diagram the contents of, put the formula:
=CellType(A1)
in row 1 of the column to the right of your active sheet that you will use to list cell contents in. Then copy across and to the right as many columns, as you have on the working part of your sheet. Then copy the formula down for as many ROWs as you are using.
Hope this gives you a start. I do not know any other way to do it? JSW
