WORKSHEET Name using a formula

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

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

The excel workboook attached [post=”643011″]Post 643011 Enhanced Workbook[/post] 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

Reply | Quote

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!

Are you looking for something like this:
=COUNTIF(SheetA2!I7:I1000,””)

Reply | Quote

Are you looking for something like this:
=COUNTIF(SheetA2!I7:I1000,””)

Sorry,It will count the blank cel instead.

Reply | Quote

Thanks again everyone.

Reply | Quote