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.

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

On Wed, 9 Jul 2008 17:49:17 +0200, "Igor" <(E-Mail Removed)> wrote:

>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.

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.

Cool!


Gord

On Wed, 9 Jul 2008 14:10:07 -0500, "Héctor Miguel"
<(E-Mail Removed)> wrote:

>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,

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?

"Héctor Miguel" wrote:

> 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.
>
>
>

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)