Excel 2010 Test Multiple Columns Balance 1

[Salut39]

Hi All,

I was wondering if someone shows me in the right direction for easier checking multiple columns in one formula if the balance goes below zero.

I used so far :

'=OR(SUM($C$69:C69)<0,SUM($D$6969)<0,SUM($E$69:E69)<0,SUM($F$69:F69)<0...

Is there another simpler way?

Thanks
Yuri

 

[SkipVought]

hi,

Yes as you copy down from a cell in row 69, if you're using this as a sheet formula, will return TRUE in the formula column on any row where the balance s negative in any of your specified columns.

And you can use that formula in Conditional Format to format those rows as well.

This is a very adequate method, unless there is some other objective not yet revealed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

Thank you

I am using it in the conditional format, but this is not very easy to manage formula. But will do for now.

Yuri

 

[SkipVought]

but this is not very easy to manage formula

And what would be the reason for that? Seems pretty straight forward.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

I have a vertical rolling cash flow with opening balances at the top and closing balances after each month, so I can't select the whole range and only can use 1 month range at the time.

 

[SkipVought]

I don't know what that means.

Please post a cogent example to illustrate this issue.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

Bank1 Bank2
Today's
Opening 100.00 200.00

Wed 01/01/14
Thu 02/01/14
Fri 03/01/14
Sat 04/01/14
Sun 05/01/14
Mon 06/01/14
Tue 07/01/14
Wed 08/01/14
Thu 09/01/14
Fri 10/01/14 (10.00)
Sat 11/01/14
Sun 12/01/14
Mon 13/01/14
Tue 14/01/14
Wed 15/01/14
Thu 16/01/14
Fri 17/01/14 (20.00)
Sat 18/01/14
Sun 19/01/14
Mon 20/01/14
Tue 21/01/14
Wed 22/01/14 100.00
Thu 23/01/14
Fri 24/01/14 300.00
Sat 25/01/14
Sun 26/01/14
Mon 27/01/14
Tue 28/01/14
Wed 29/01/14
Thu 30/01/14
Fri 31/01/14

MonthEnd 01 190.00 480.00

Sat 01/02/14
Sun 02/02/14
Mon 03/02/14
Tue 04/02/14
Wed 05/02/14
Thu 06/02/14
Fri 07/02/14
Sat 08/02/14
Sun 09/02/14
Mon 10/02/14
Tue 11/02/14
Wed 12/02/14
Thu 13/02/14
Fri 14/02/14
Sat 15/02/14
Sun 16/02/14
Mon 17/02/14
Tue 18/02/14
Wed 19/02/14
Thu 20/02/14
Fri 21/02/14
Sat 22/02/14
Sun 23/02/14
Mon 24/02/14
Tue 25/02/14
Wed 26/02/14
Thu 27/02/14
Fri 28/02/14

MonthEnd 02 190.00 480.00

 

[SkipVought]

It seems that you have posted a column for DATE, a column for Bank1 transactions and a column for Bank2 transactions for 2 months, and presumable the list goes on IN THE SAME COLUMNS for future months.

So it seems that this is the column distribution for the FIRST month ( and other months would follow in the same 3 columns...
[pre]
Bank1 Bank2
Today's
Opening 100 200

Wed 1/1/2014
Thu 1/2/2014
Fri 1/3/2014
Sat 1/4/2014
Sun 1/5/2014
Mon 1/6/2014
Tue 1/7/2014
Wed 1/8/2014
Thu 1/9/2014
Fri 1/10/2014 -10
Sat 1/11/2014
Sun 1/12/2014
Mon 1/13/2014
Tue 1/14/2014
Wed 1/15/2014
Thu 1/16/2014
Fri 1/17/2014 -20
Sat 1/18/2014
Sun 1/19/2014
Mon 1/20/2014
Tue 1/21/2014
Wed 1/22/2014 100
Thu 1/23/2014
Fri 1/24/2014 300
Sat 1/25/2014
Sun 1/26/2014
Mon 1/27/2014
Tue 1/28/2014
Wed 1/29/2014
Thu 1/30/2014
Fri 1/31/2014

MonthEnd 1 190 480
[/pre]
Is that a correct assumption?

And if so, where does this CF occur. Have not got the picture yet!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

That's correct, I have CF on the right hand side, if balance go <0 highlight cell in red.

The issue here is
=OR(SUM([highlight #FCE94F]$C$4[/highlight]:C37)<0,SUM($D$437)<0
I have to change
=OR(SUM([highlight #FCE94F]$C$39[/highlight]:C68)<0,SUM($D$3968)<0,

 

[Salut39]

I think I found the solution: I have to use SUBTOTAL() instead of SUM() in both MonthEnd Rows and CF Formulas - seem to be working.

Open for more suggestions.

Thanks
Yuri

 

[SkipVought]

Don't know why you're using the OR() to include other columns as well?

Don't you want to see where EACH individual bank goes negative???

Or do you really want ALL the bank columns to go shaded when ANY bank does negative?

1. For the EACH approch, Select your entire column of data starting in column C row 4.

2. Open the CF wizard and modify your formula as for EACH:
[tt]
=SUBTOTAL(9,C$4:C4)<0
[/tt]

3. Use the Format Painter to COPY this format and PASTE it over any orher column that you want to format.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

This one is good.

My idea was to have a column on the side (not actual column) with CF and test if any of the columns goes negative. that's why I used OR().

Thank you

 

[SkipVought]

My idea was to have a column on the side ([highlight]not actual column[/highlight])

???
What did you have in mind, a [highlight]virtual column[/highlight]? Unfortunately not a feature in Excel.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

Empty column with CF on the side of data table.

 

[SkipVought]

So you previously stated that your CF column was on the RIGHT side of the data table.

How many columns of Bank data to compare in the CF formula?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

I have columns from C to T, CF in Column U. Each column is separate account.

 

[Salut39]

=OR(SUBTOTAL(9,$C$4:C4)<0,SUBTOTAL(9,$D$44)<0,SUBTOTAL(9,$E$4:E4)<0,SUBTOTAL(9,$F$4:F4)<0,SUBTOTAL(9,$G$4:G4)<0,SUBTOTAL(9,$H$4:H4)<0,SUBTOTAL(9,$I$4:I4)<0,SUBTOTAL(9,$J$4:J4)<0,SUBTOTAL(9,$K$4:K4)<0,SUBTOTAL(9,$L$4:L4)<0,SUBTOTAL(9,$M$4:M4)<0,SUBTOTAL(9,$N$4:N4)<0,SUBTOTAL(9,$O$4:O4)<0,SUBTOTAL(9,$P$44)<0,SUBTOTAL(9,$Q$4:Q4)<0,SUBTOTAL(9,$R$4:R4)<0,SUBTOTAL(9,$S$4:S4)<0,SUBTOTAL(9,$T$4:T4)<0)

This is the actual formula in CF (Column(U))

 

[SkipVought]

I can't see any other way to simplify what you already have except a macro.

Te 3 CONST (constants define the heading row, the start column for account balances and the end column

Sub CF_AccountsSummary()
    Dim c As Range, r As Range, cSUM As Currency
    Const HEAD_ROW = 3
    Const START_COL = "C"
    Const END_COL = "T"
    
    With ActiveSheet
        For Each c In .Range(.Cells(HEAD_ROW, START_COL), .Cells(HEAD_ROW, END_COL))
            If c.Column = 17 Then
                cSUM = cSUM
            End If
            cSUM = 0
            For Each r In Intersect(c.EntireColumn, .Range(c.Offset(1), c.Offset(.Cells.Rows.Count - HEAD_ROW)), .UsedRange)
                cSUM = cSUM + r.Value
                With .Cells(r.Row, END_COL + 1)
                    If cSUM < 0 Then
                        .Value = 1
                    End If
               End With
            Next
        Next
    End With
End Sub

COPY it.
Right-Click the sheet tab and select View Code
PASTE the code into the sheet code window
Look for the DROP DOWN just ABOVE and to the LEFT, and select Worksheet
Look for the DROP DOWN just ABOVE and to the RIGHT, and select Change
You will see this code

Private Sub Worksheet_Change(ByVal Target As Range)
    
End Sub

Enter your macro name here.

Private Sub Worksheet_Change(ByVal Target As Range)
   [b]Application.EnableEvents = False
   CF_AccountsSummary
   Application.EnableEvents = False[/b]
End Sub

alt+F11 toggles between the sheet and the VB Editor.





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Salut39]

Thank you

Much appreciated!