Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Tab name when cell is blank
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 |
#2
|
|||
|
|||
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) "Jet" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Jet" wrote in message ... 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 |
#4
|
|||
|
|||
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!A10,"","Sheet2!") ? Seems to work for me |:) "Jet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look for change next blank cell in Range | Excel Worksheet Functions | |||
Go to first blank cell | Excel Worksheet Functions | |||
How would I fill blank cells with the data from a previous cell? | Excel Discussion (Misc queries) | |||
How to read a blank cell as zero for formulas? | Excel Discussion (Misc queries) | |||
Help with formula to make cell blank | Excel Worksheet Functions |