List of worksheets

[Igor]

Hello

I'm wondering if it's possible to make a list of all worksheets using Excel
functions only. Assuming the first worksheet is "TheList" I want to make, in
that workbook, a list of all worksheets right from that worksheet using
functions only?
Is it possible?

Thanks for any help.

 

[Gord Dibben]

None that I know using Excel functions only.

Usually VBA macro is employed to get a list of sheets.

Private Sub ListSheets()
Dim rng As Range
Dim I As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "TheList"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(I, 0).Value = Sheet.Name
I = I + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP

 

[Héctor Miguel]

hi, Igor !

I'm wondering if it's possible to make a list of all worksheets using Excel functions only.
Assuming the first worksheet is "TheList" I want to make, in that workbook
a list of all worksheets right from that worksheet using functions only?
Is it possible?

1) be sure "TheList" sheet is the first (index) in the workbook

2) put a title in A1 (i.e. Sheets in this workbook)

3) define/create a name (menu: insert / names / define...)
name: n_Sheets
formula: =get.workbook(1+0*now())

4) get the list with the following formula:
[A2] =choose(1+(row()>counta(n_sheets)),mid(index(n_sheets,row()),search("]",index(n_sheets,1))+1,31),"")

5) copy/drag/... A2-formula n_rows down (as needed)

hth,
hector.

 

[Gord Dibben]

Cool!


Gord

hi, Igor !

I'm wondering if it's possible to make a list of all worksheets using Excel functions only.
Assuming the first worksheet is "TheList" I want to make, in that workbook
a list of all worksheets right from that worksheet using functions only?
Is it possible?

1) be sure "TheList" sheet is the first (index) in the workbook

2) put a title in A1 (i.e. Sheets in this workbook)

3) define/create a name (menu: insert / names / define...)
name: n_Sheets
formula: =get.workbook(1+0*now())

4) get the list with the following formula:
[A2] =choose(1+(row()>counta(n_sheets)),mid(index(n_sheets,row()),search("]",index(n_sheets,1))+1,31),"")

5) copy/drag/... A2-formula n_rows down (as needed)

hth,
hector.

 

[Don]

Héctor,

Your formulas work great to return the names of all of the sheets to the
right of the first sheet. However, can your formula below be modified to
return the contents of cell A1 on all sheets to the right of the first sheet?

hi, Igor !

I'm wondering if it's possible to make a list of all worksheets using Excel functions only.
Assuming the first worksheet is "TheList" I want to make, in that workbook
a list of all worksheets right from that worksheet using functions only?
Is it possible?

1) be sure "TheList" sheet is the first (index) in the workbook

2) put a title in A1 (i.e. Sheets in this workbook)

3) define/create a name (menu: insert / names / define...)
name: n_Sheets
formula: =get.workbook(1+0*now())

4) get the list with the following formula:
[A2] =choose(1+(row()>counta(n_sheets)),mid(index(n_sheets,row()),search("]",index(n_sheets,1))+1,31),"")

5) copy/drag/... A2-formula n_rows down (as needed)

hth,
hector.

 

[Héctor Miguel]

hi, Don !

Your formulas work great to return the names of all of the sheets to the right of the first sheet.
However, can your formula below be modified to return the contents of cell A1 on all sheets to the right of the first sheet?

you can use "the list" of worksheets giving another name to that range
(excluding title and blanks or whatever used)
nesting indirect function within sumproduct(sumif(..."!a1"...

there are a lot of examples on the web (i.e. -> http://tinyurl.com/6rzdc6)

hth,
hector.

__ OP __

I'm wondering if it's possible to make a list of all worksheets using Excel functions only.
Assuming the first worksheet is "TheList" I want to make, in that workbook
a list of all worksheets right from that worksheet using functions only?
Is it possible?

1) be sure "TheList" sheet is the first (index) in the workbook

2) put a title in A1 (i.e. Sheets in this workbook)

3) define/create a name (menu: insert / names / define...)
name: n_Sheets
formula: =get.workbook(1+0*now())

4) get the list with the following formula:
[A2] =choose(1+(row()>counta(n_sheets)),mid(index(n_sheets,row()),search("]",index(n_sheets,1))+1,31),"")

5) copy/drag/... A2-formula n_rows down (as needed)