Tab name when cell is blank

[Guest]

I have a multiple worhseet workbook. I need to see the tab name for those
worksheet that have a specific blank cell. So, within a range of worksheets
I need the tab name for those where A1 is blank.

Your help is greatly appreciated

Regards from Montreal

 

[Bob Phillips]

Not really sure what you mean, do you want a list of all such sheets (which
will need VBA), or do you want a formula that will test a value in a sheet
(which needs the sheet name to test - circuitous).

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

You can use a custom VBA function like the following:

Function GetSheetNames() As Variant
Application.Volatile True
Dim Arr() As String
Dim Ndx As Long: Ndx = 1
Dim WS As Worksheet
ReDim Arr(1 To Application.Caller.Parent.Parent.Worksheets.Count)
For Each WS In Application.Caller.Parent.Parent.Worksheets
If WS.Range("A1").Value = "" Then
Arr(Ndx) = WS.Name
Ndx = Ndx + 1
End If
Next WS
If Application.Caller.Rows.Count = 1 Then
GetSheetNames = Arr
Else
GetSheetNames = Application.Transpose(Arr)
End If

End Function

Select the cells in which you want the worksheet names to appear
(this range should contains the same number of cells as the total
number of worksheets), type the formula =GetSheetNames() and
press Ctrl+Shift+Enter rather than just Enter.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Maybe I'm missing the point or perhaps I'm oversimplifying the issue, but why
not just use a formula on Sheet1 such as :

=IF(Sheet2!A1>0,"","Sheet2!") ?

Seems to work for me |:>)