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?
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?