finding first non-zero value in same cell location in 31 sheets

[donh]

using excel 2010 - - - we have a daily deposit spreadsheet that 50 different companies use. The daily totals are linked to the respective 31 day tabs in the master spreadsheet where a daily reconciliation is done to each company's system balance.

The summary sheet on the master has a 3d formula of those 31 tabs for monthly totals. The reconciliation to the system is done in column W on each sheet and hopefully that value is 0

Is there a way to find the first instance where cell W7 does not equal 0 and if so drop in the tab number (one tab for each day of month) - - - keeping in mind that if day 3 is out of balance and that is remedied then it's possible that day 10 is out of balance and then 10 should be returned. This way you don't have to click tab to tab to find the days that are out of balance

I built an IF formula for 31 days but have found that you cannot nest more than 8
=IF('1'!W7<>0,1,IF('2'!W7<>0,2,IF('3'!W7<>0,3,IF('4'!W7<>0,4,IF('5'!W7<>0,5,IF('6'!W7<>0,6,IF('7'!W7<>0,7,IF('8'!W7<>0,8,""))))))))

IFS is not a function that is available and we would need to be able to use the formula for each company and not just limited to W7 where the fist companies balance is.

Can someone please help?

 

[Anthony47]

You could use the following approach, that don't nest functions:

="#"&if(Sheet1!W7<>0,"-1","")&if(Sheet2!W7<>"","-2","")&....

This should create a string of numbers that each points to a failing sheet; for example
#-6-20
Means that 6th and 20th sheets are in error

Or you can switch to a User Defined Function, that corresponds to this code:

Function FailingW7(ByRef Initz As Variant, ByRef Endz As Variant, Optional ByVal Dummy As Variant) As String
Dim iSh As Long, eSh As Long, I As Long
Dim CkCell As String, OStr As String
'
CkCell = "W7"       '<<< The cell to check on each worksheet
'
OStr = "#"
iSh = Initz.Parent.Index
eSh = Endz.Parent.Index
For I = 1 To ThisWorkbook.Worksheets.Count
    If Sheets(I).Type = xlWorksheet Then
        If Sheets(I).Range(CkCell).Value <> 0 Then
            OStr = OStr & "-" & Sheets(I).Name
        End If
    End If
Next I
FailingW7 = OStr & "#"
End Function

Put this in a standard vba module of your workbook
Then you can use the formula:

=FailingW7(InitialSheet!A1,LastSheet!A1)

It will return a string in the format #-FailingSheetName-FailingSheetName-…#
However this formula will not recalculate automatically if something is modified in the workbook, you have to press F2 and Enter to reenter the formula

A possible improvement can be obtained by using the following format

=FailingW7(InitialSheet!A1,LastSheet!A1,Now())

In this case the formula recalculate its output whenever the user inputs any value or the worksheet anyway recalculates

Bye

 

[donh]

I was able to make it work! Very much appreciated!