Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a WORKBOOK with several sheets. I need to add a summary sheet at the beginning as the 1st sheet and list all workSHEET names in one column. These SHEETS have specific names like xy248, vr 269 etc. Can this be done with a formula that I can put in a cell and copy down on the summary sheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could run this macro:

    Code:
    Sub ListWorksheetNames()
      Dim i As Integer
      Worksheets("SummarySheet").Range("A:A").ClearContents
      For i = 1 To Worksheets.Count
    	Worksheets("SummarySheet").Range("A" & i) = Worksheets(i).Name
      Next i
    End Sub
    where SummarySheet is the name of the summary worksheet
    If you have added, removed or renamed worksheets, simply run the macro again.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Worked fine.

    Now that I have my list, I'm trying to count the number of blank cells on each worksheet in column I "From I7 to i1000

    I tried in column b beside each listing generated by the macro:

    =countif(sheetA2I!I7:I1000,>"") but it didn't work ---A2 in the refers to the sheet name generated by the formula, I intended to copy down as far as needed thus getting the count for each sheet in the workbook or will have to use code for this also???

    thanks a heep!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use the INDIRECT function for this.

    To count the number of non-blank cells:

    =COUNTA(INDIRECT("'"&A2&"'!I7:I1000"))

    To count the number of blank cells:

    =COUNTBLANK(INDIRECT("'"&A2&"'!I7:I1000"))

    These formulas can be filled down.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stans' post='790117' date='21-Aug-2009 19:08']Worked fine.

    Now that I have my list, I'm trying to count the number of blank cells on each worksheet in column I "From I7 to i1000

    I tried in column b beside each listing generated by the macro:

    =countif(sheetA2I!I7:I1000,>"") but it didn't work ---A2 in the refers to the sheet name generated by the formula, I intended to copy down as far as needed thus getting the count for each sheet in the workbook or will have to use code for this also???

    thanks a heep![/quote]
    Are you looking for something like this:
    =COUNTIF(SheetA2!I7:I1000,"")
    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='790120' date='21-Aug-2009 19:21']Are you looking for something like this:
    =COUNTIF(SheetA2!I7:I1000,"")[/quote]
    Sorry,It will count the blank cel instead.
    Regards
    Prasad

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790119' date='21-Aug-2009 08:46']You can use the INDIRECT function for this.

    To count the number of non-blank cells:

    =COUNTA(INDIRECT("'"&A2&"'!I7:I1000"))

    To count the number of blank cells:

    =COUNTBLANK(INDIRECT("'"&A2&"'!I7:I1000"))

    These formulas can be filled down.[/quote]


    Worked beutifully
    This will save me a ton of time---thank you so much!!!!!

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='stans' post='790127' date='21-Aug-2009 10:04']Worked beutifully
    This will save me a ton of time---thank you so much!!!!![/quote]

    The excel workboook attached Post 643011 Enhanced Workbook could also be of assistance. It will give you hyperllinks to each Tab and will automatically update when you save the file.

    Regards,

    Tom Duthie

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again everyone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •