List of worksheets #2

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. 

0
igorm1 (21)
7/9/2008 3:49:17 PM
excel 39879 articles. 2 followers. Follow

5 Replies
614 Views

Similar Articles

[PageSpeed] 56

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" <igorm@live.com> 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. 

0
Gord
7/9/2008 6:46:13 PM
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. 


0
7/9/2008 7:10:07 PM
Cool!


Gord

On Wed, 9 Jul 2008 14:10:07 -0500, "H�ctor Miguel"
<NOhemiordiSPAM@PLShotmail.com> 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. 
>

0
Gord
7/10/2008 3:08:43 AM
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. 
> 
> 
> 
0
Don4849 (203)
7/10/2008 1:36:01 PM
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) 


0
7/10/2008 10:01:45 PM
Reply:

Similar Artilces:

Customizing Activities\History LIst
Is there a way to add a date to Activities' History list? It would be nice to know when each activity happened. Thanks, Troy Troy, no way currently, although Microsoft has mentioned it might be an option in version 2.0 (nothing is guaranteed!) A few ISVs have custom products that do it... www.salentica.com www.c360.com Dave "Troy" <anonymous@discussions.microsoft.com> wrote in message news:af6b01c436bd$ce671f00$a001280a@phx.gbl... > Is there a way to add a date to Activities' History > list? It would be nice to know when each activity > happened. >...

Better to "nest" or use multi-level list?
I'm in the process of creating/defining a style(s) for a list that will be numbered in "level 1" and have indented bullets in "level 2." For such a list is it better to define a multi-level list style and linked paragraph styles? Or use one's already defined numbered list and use one of the Word's pre-defined bullet lists when needed for the second level? As part of this question, is is preferable not to have a style "nested" within another style or doesn't it matter? Thank you. -- Norm Hi Norm: I'm not sure why yo...

Distribution List Changes
Hi All, I have a mail enabled distribution list with four users. Only those users plus the Administrator have rights on this group. Yet a seperate user is able to add / edit or remove users to this group even though they do not belong to this group. The user does not even have admin rights and is able to make changes. Let me know of any options I can try. Thanks Waynear wrote: > Hi All, > > I have a mail enabled distribution list with four users. Only those > users plus the Administrator have rights on this group. > > Yet a seperate user is able to add / edit or remov...

Sound shows on custom Annimation list yet no sound heard
When the PP Presentation is saved as a show,some slides have sound and some do not. All sound came from the same folder. Also, when playing the slides while in PPPresentation, swome have sound and some do not despite all being indicated on custom annimation lisy and all startin "after previous." Thanks! Often when one sound plays but another doesn't, it's an issue of the length of the file path. What's the file path to your music? Is it really long? If so, the longer-named songs might just be hitting the length limit. -- Echo [MS PPT MVP] http://www.ec...

Drop Down List and Check Box
Hi...I was search for this topic but don`t found the solve...Anyway I have a drop down list like this [image: http://www.geocities.com/augurtrade/excel_checkbox.txt] and this [image: http://www.geocities.com/augurtrade/excel_checkbox2.txt] ok my questions is what is (Blanks) and (NonBlanks)...? Is it possible for me to see only all the check box that was checked or vice versa...??? How to make a total below the checkbox i mean when someone check the check box, a total number appear below the last check box [image: http://www.geocities.com/augurtrade/excel_checkbox3.txt] [image: http://www.g...

how reliable is the "Who has you in their contact list" list?
Because someone who I think has recently (within the last week) deleted me is still on this list (I don't think she blocked me), but when I check who deleted me on blockstatus.com, he shows up. could it be it's not updated? which of the two would be more reliable? -- maykk Greetings, Do not use these websites, they will steal your identity and sell it. You should change your password immediately. That said, in general, the "who has..." list within Messenger is accurate. There have been instances over the last six-months or so where the list (and m...

Continue Numbering #2
I'm wondering if the "Continue numbering" option is in Publisher as it is in Word? Example: I start creating a numbered list. After the second item, I need to insert a few bullets. I then want to go back to the numbered list. Word has a smart tag, asking if you want to continue numbering. Is there a similar feature in Publisher? Right now, I have to go to Format, Bullets & Numbering and change the start number every time this happens. ...

How can I set up a shared excel worksheet and work offline
I have a group of laptops on a windows 2003 network. I have set up shared Excel Workbooks to allow users to modify different worksheets at the same time. I would now like to take this a step further. I want to allow two or more uses to take a copy of the shared workbook away from the offce with each user being able to work on their part and then be able to save them back to the network with all changes being saved. I was hoping the "make available offline" feacher would work but it seems that it will not. Please give any suggestions of workarounds or other software that ma...

Outlook Business Contact Manager anniversaries showing up in "OutLook Today" #2
Hi, I just imported about a 1000 customer records into a Business Contact Manager database so I could start sending emails to my clients (as well as perform other tasks). I was appalled to see all the anniversary dates being displayed in my Outlook Today page. Is there anyway to prevent BCE birthday and anniversary dates from being displayed in Outlook Today? I only want my normal contact info to appear here. Thanks! Emile ...

Selecting from contacts list
I have all my contacts in the Contacts List and I want to delete all those with a common extension i.e. all those who have email addresses @hotmail. I am using Outlook 2007 Can you do a search or advanced search which will show the contact you want to delete? You should be able to highlight & delete them from the results. "GrahamC" <GrahamC@discussions.microsoft.com> wrote in message news:6AC1880D-9632-4ED9-BAE2-7083690B9FA7@microsoft.com... > I have all my contacts in the Contacts List and I want to delete all those > with a common extension i.e. all t...

Can worksheet data be exported/imported to/from flat file?
We have a software product which does structured data storage and retrieval of application data to and from the database. (Like almost any other application - nothing new here.) To allow customers to do ad-hoc extensions, we have a module which allows them to integrate their own spreadsheets. Alternatively, the client may contract with us to develop custom spreadsheets for them. This allows us to quickly integrate custom 'interfaces' with our app, according to each customer's needs. The problem with this, is that the 'code' (in our context VBA) is stored repetitively with ...

Import problem #2
In trying to import an old .pst backup into Outlook 2003 from a CD I get an error that denies me access to those files. I'm pretty sure I specified no encryption (and no compression) when I created the backup, and I didn't set a password. The backup may have been created using Office 2000, but I don't see what difference that should make. So why can't I get into my own files? TIA for any help. Tom S In news:Q_O2g.4927$Lm5.2978@newssvr12.news.prodigy.com, Tom S <toms@pacbell.net> typed: > In trying to import an old .pst backup into Outlook 2003 from a CD ...

Script to list access right
Hi I am trying to find any script which would generate list of login users with certain access level (select, write etc.) at one particular database. Is there something like that? Thanks. Justin ...

Want graph with 2 axis, but bars overlap when second axis added
I need to have a bar graph with two data sets of different scales, yet when I change one data set to an secondary axis, the two bars combine. I need them to be sitting side by side. thanks Hi, Here is one example. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Gill wrote: > I need to have a bar graph with two data sets of different scales, yet when I > change one data set to an secondary axis, the two bars combine. I need them > to be sitting side by side. > > thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Actually, th...

wrong character set? #2
I have a MFC dll project. When the dll is called, a dialog is poped up. CDialog_Dll::CDialog_Dll(CParameters *parmList, CWnd* pParent / *=3DNULL*/) : CDialog(CDialog_Dll::IDD, pParent) { m_parmList =3D parmList; Create (IDD_DIALOG_MAIN,pParent);//Set break point 1 here ShowWindow (SW_SHOW);//Set break point 2 here }//Set break point 3 here BOOL CDialog_Dll::OnInitDialog() { CDialog::OnInitDialog(); return true;//Set break point 4 here } All above works fine. CParameters is cumtomer class. The problem is coming when I click a button called 'load': void CDialog_Dll::OnBnClick...

a list of lists
currently i use CArray to keep track of a list of objects, but I don't know MFC classes well enough to keep track of a list of lists. I'd appreciate some suggestions "wanwan" <ericwan78@yahoo.com> ha scritto nel messaggio news:1187535523.924671.143350@j4g2000prf.googlegroups.com... > currently i use CArray to keep track of a list of objects, but I don't > know MFC classes well enough to keep track of a list of lists. I'd > appreciate some suggestions You might want to embedd your list based on CArray into a class (e.g. CListOfSomething), and then ...

Smart List export to Excel #2
Hi, We are using Dynamics 9.0 and Office 2003. When we try to do a Smart List export to Excel, we keep getting an "Exception_Class_Object_Exception" that references varying object errors, such as 'Cells' or 'Value'; the object errors will change each time we try an export. As a workaround, I've found that closing Excel before doing the export allows the export to complete successfully. However, that solution isn't acceptable, as our Dynamics users would like to be able to work in Excel, doing other tasks, while the export is processing. Does anyone...

List of different values in data area
Hi excel specialists, How Can I automatically get the list of different values from the dat area and to find out their frequence? INDIVIDUALLY MEASURED VALUES: 3,5 4 3,5 4 3 3,5 4 4 3 3,5 4 3 3,5 3 3 3,5 4 4 Thanks for your help in advance. Balcovja -- balcovj ----------------------------------------------------------------------- balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623 View this thread: http://www.excelforum.com/showthread.php?threadid=39572 Take a look at FREQUENCY in Hep -- HTH RP (remove nothere from the email address if m...

Formatting a pivot worksheet to display a string at the end!
Hi there, I have created several worksheets using the 'Show Pages' function, but now need to add a bit of blurb underneath each of the pivot tables. I was thinking of doing this in the Page Footer, but this will be displayed on every page - and some worksheets will span several pages. Can anyone tell me if there is an easy and quick way of adding some text to the end of the pivot tables in every worksheet (there are 50 worksheets!) thanks in advance, Matt -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum...

Fields in items list-- add/delete
Hai, I am using RMS version 1.2. In Stores Operations Manager it displays list of items when click on Database-->Items. It displays only five columns. Now i need to add / delete some fields in this list. Is it possible to add or delete the fields? if yes how can i add the fields? Thanks in advance, Nagendra On Wed, 27 Apr 2005 01:22:02 -0700, "Nagendra" <Nagendra@discussions.microsoft.com> wrote: >Is it possible to add or delete the fields? if yes how can i add the fields? I don't think so antonio ...

how to link to external worksheet
I am using excel 2003 and am trying to link a worksheet from one spreadsheet (source) into a second spreadsheet (destination). The source spreadsheet may or may not be open when accessing the link from the destination spreadsheet. I figured out to go to the source, select the whole sheet, ^c, then switch to a blank worksheet in the destination and Edit->Paste->Paste Special. From there I "Paste link", then paste "format" and "column widths". My first problem is that any blank cells on the source worksheet come over as 0's on the destination, ev...

Global Address List and Handhelds
We use Ipaqs throughout the company, and I was wondering if there is anyway to have exchange add the GAL to the outlook contacts on the workstation, so that when they sync up, they will retrieve the Global Address List? In news:%23TkYtT0$GHA.3560@TK2MSFTNGP04.phx.gbl, Flip <phil.atkinson@beltek.com> typed: > We use Ipaqs throughout the company, and I was wondering if there is > anyway to have exchange add the GAL to the outlook contacts on the > workstation, so that when they sync up, they will retrieve the Global > Address List? No (and this wouldn't really be an ...

remove small list from large list
I have two mail lists on excel. The first has 16,000 names, the second has about 6,000. I need to remove the 6,000 from the 16,000 as they all appear in the big list as well. How do I extract those 6,000 entries from the larger list? My expected result is a mailing list with 10,000 names. Chip Pearson has some techniques at: http://www.cpearson.com/excel/duplicat.htm Howie J. wrote: > > I have two mail lists on excel. The first has 16,000 names, the second has > about 6,000. I need to remove the 6,000 from the 16,000 as they all appear > in the big list as well. How do ...

Is this possible? #2
I have 2 PC's. I wish to have email messages come to both PC's that share a common email ID. After both arrive, I want the server to purge the email. Is there a way to make sure that both PC's received the same email message and then would automatically delete the message off the server? ...

Popup message #2
Thank you Juli -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=26376 you're welcome "SMILE" <SMILE.1d8kdb@excelforum-nospam.com> wrote in message news:SMILE.1d8kdb@excelforum-nospam.com... > > Thank you Julie > > > -- > SMILE > ------------------------------------------------------------------------ > SMILE's Profile: > http://www.excelforum.com/membe...