Checking if a row is blank on every worksheet

[Vipul1992]

I have a excel workbook having 70 worksheets. Now, I want to check if Row 13 on every worksheet is blank or not. If not blank, then I want name of that worksheet. Is there any excel formulae to get that detail without checking every worksheet separately?

 

[Radoslaw Poprawski]

here is my Way:
First you need to grab all the names of sheets.

There is Macro to do so.

[

CODE]Sub SheetNames()
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 2) = Sheets(i).Name
Next i
End Sub

[/CODE]


Now all the names of Sheets are in column 1 range.
Create Table out of it. Either by CTRL+T with no headers, or this macro:

'Create Dynamic Table in Excel VBA
Sub VBAF1_Create_Dynamic_Table()
    
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim TblRng As Range
    
    'Sheet Name
    With ActiveSheet
    
            
        'Find Last Row
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Find Last Column
        lLastColumn = 1
        
        'Range to create table
        Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
        
        'Create table in above specified range
        Set tableListObj = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        
        'Specifying table name
        tableListObj.Name = "FirstDynamicTable"
        
        'Specify table style
        tableListObj.TableStyle = "TableStyleMedium14"
    End With
    
    'Display message on the screen
   ' MsgBox "Table has created successfully.", vbInformation, "VBAF1"
 
 End Sub

Now that is our Basic formula to start:

=--NOT(ISBLANK(Sheet2!13:13))

now we need to make it dynamic:

=
SUM(NOT(ISNUMBER(--INDIRECT(T_Names_of_Sheets[@[Sheet Names]]&"!13:13")))+0)

Remember that the 1st column is a table.

Now for the info:

=IF(SUM(NOT(ISNUMBER(--INDIRECT(T_Names_of_Sheets[@[Sheet Names]]&"!13:13")))+0);"There is something here";"It Is empty")

and with the hyperlink to the not empty row 13 sheet:

=IF(
SUM(
NOT(
ISNUMBER(
--INDIRECT(T_Names_of_Sheets[@[Sheet Names]]&"!13:13")))+0);
HYPERLINK("#"&T_Names_of_Sheets[@[Sheet Names]]&"!A13";"There is something here");
"It Is empty")

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	A	B	C
1	Sheet Names	Is It blank?
2	Sheet1	It Is empty
3	Sheet2	There is something here
4	Sheet3	It Is empty
5
Sheet1

Cell Formulas
Range	Formula
B2,B4	B2	=IF(SUM(NOT(ISNUMBER(--INDIRECT(T_Names_of_Sheets[@[Sheet Names]]&"!13:13")))+0),"There is something here","It Is empty")
B3	B3	=IF( SUM( NOT( ISNUMBER( --INDIRECT(T_Names_of_Sheets[@[Sheet Names]]&"!13:13")))+0), HYPERLINK("#"&T_Names_of_Sheets[@[Sheet Names]]&"!A13","There is something here"), "It Is empty")

However I was not able to figure out spilled arrey function.
Sorry about that.

hmmm
if possible can some1 figure out spilled arrey function for b2? I gladly learn.