List of Sheetnames or TOC w/out VBA or ADD Ins

[SuzyQPA2]

Is it possible to create a list of sheetnames or a Toc on a summary sheet for formula use iwthout using VBA or Add-ins?"

Does anyone know?

I can create a list manually but the problem is that if they add a spreadsheet it doesn't get updated.

or a formula that says if a tab (sheetname) exists that is between this range show here. (its if there is 31 days vs 30 that causes the extra sheet)

My whole project is to take 9 monthly workbooks of daily activity (summarize them by month) and then show them on a consolidated summary sheet.

So what i was trying to do was build a summary sheet in each workbook. When they copy the old workbook to make the new voila - the summary sheet is there.
I can then link to the summary each month for my consolidated sheet.

 

[SuzyQPA2]

Re: List of Sheetnames or TOC - Macro?

Is it possible to create a list of sheetnames or a Toc on a summary sheet for formula use iwthout using VBA or Add-ins?"

Does anyone know?

I can create a list manually but the problem is that if they add a spreadsheet it doesn't get updated.

or a formula that says if a tab (sheetname) exists that is between this range show here. (its if there is 31 days vs 30 that causes the extra sheet)

My whole project is to take 9 monthly workbooks of daily activity (summarize them by month) and then show them on a consolidated summary sheet.

So what i was trying to do was build a summary sheet in each workbook. When they copy the old workbook to make the new voila - the summary sheet is there.
I can then link to the summary each month for my consolidated sheet.

It seems the answer to this question is probably no.
I'm worried about add-in's and macros because other people update the file and send it back to me. Is it possible to use a Macro to create a TOC if your users don't have any add ins etc..

 

[fairwinds]

Hi,

Maybe something like this as sheet code for the summary sheet:

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Range("A2:A33").ClearContents
x = 2
For Each ws In ThisWorkbook.Sheets
Cells(x, 1) = ws.Name
x = x + 1
Next ws
End Sub

 

[Juan Pablo González]

Actually, it's not that hard. Go to Insert | Name, Define, and type in the first box

Sheets

and in the Refers To

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

click on 'Add' then on 'Close'

Now, in the summary sheet, select A2 for example, and put this formula

=IF(COUNTA(Sheets)>=ROW($A1),INDEX(Sheets, ROW($A1)), "")

and drag it down to say, A50 (Which gives you "room" for 49 sheets.

Now, you can use those cells with INDIRECT functions like

=INDIRECT("'" & A2 & "'!B2")

to return cell B2 of each sheet.

Does this work for you ?

 

[SuzyQPA2]

I've never used Macros or VBA before my prior job was less demanding and interesting. LOL

How do i put this in? I tried copying it in the code section of VB View for the summary sheet but nothing happened.....bet i left something out right?

 

[fairwinds]

Right click on the sheet tab for the summary sheet - view code.

Paste the code in the empty window that appears.

The code will run automaticly when you select the summary sheet.


Check out Juan Pablo's suggestion too. It's pretty cool and does not require code.

 

[SuzyQPA2]

Thanks!

I tried Juan's suggestion and it works great.
This will be really helpful because even though we came up with a universal naming convention for the sheets some people enter in extra spaces