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

donh

Board Regular
Joined
May 7, 2002
Messages
151
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You could use the following approach, that don't nest functions:
Code:
="#"&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:
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:
Code:
=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
Code:
=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
 
Upvote 0

Forum statistics

Threads
1,224,803
Messages
6,181,055
Members
453,015
Latest member
ZochSteveo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top