Auto sum invoice amounts when invoices are still outstanding?

[Loulax07]

this is driving me crazy. i use an Excel sheet for accounting purposes for my business. basically the cells are from left to right- Invoice #, invoice date, invoice $, date received. Before they're received, I highlight the cell of the invoice # in yellow to show me that it's outstanding and to follow up with the customers. What I want to do, at any one time, is show how much we have outstanding in open invoices and not sure how to do it.

One way I thought was to add another cell, with a pull down of either Yes or No. Yes= invoice paid, No= invoice open. I can then create an IF THEN statement or something that would only add that $ value into a sum if it's still open or No was selected.
The other way I thought which I know is more complicated is to have it sum only when the invoice # is highlighted in yellow.

Any thoughts?

 

[Loulax07]

 

[Logit]

.
.
I used two additional columns: F & G

In F2, paste this formula, then copy down:

=IF(GetFillColor(B2)=6,"DUE", IF(GetFillColor(B2)<>6,"", ""))

.
.
In G2, paste this formula, then copy down:

=IF(ISNUMBER(SEARCH("DUE",F2)),D2,"")

.
.
For demonstration purposes, in G31, paste this formula:

=SUM(G2:G28)

.
.
To make the formulas in column F work as desired, create a routine module in the VBE window and paste this code:

Option Explicit

Function GetFillColor(Rng As Range) As Long
    GetFillColor = Rng.Interior.ColorIndex
End Function

A sample of the project can be downloaded here : https://www.amazon.com/clouddrive/s...KqRIbQWrIJZuzktEtF?ref_=cd_ph_share_link_copy


Please let me know how you make out.

 

[Loulax07]

Thanks! I tried it but when I changed the fill color from yellow to none and inserted a date in column E, it didn't change the F column away from "DUE" or erase the dollar value in column G. Am I doing something wrong?

 

[Logit]

.
.
Ok ... here is a different approach using a VBA macro.

Paste the following code into a routine Module. You activate the macro with a Command Button positioned on the Sheet.

Sub ChangeColor()Dim lRow
Dim MR
Dim cell
Dim OffSet
lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
    If cell.Interior.ColorIndex = 6 Then
        cell.OffSet(, 4) = "DUE": cell.OffSet(, 5) = cell.OffSet(, 2)
    Else
        cell.OffSet(, 4) = "": cell.OffSet(, 5) = ""
    End If
Next
End Sub

It's also possible to further automate the process by placing this macro instead, into the Worksheet_Change event. When a cell in Col B is highlighted Yellow
the macro will fire. The same if the cell is voided of a fill color. The downside to this approach though, is every time you click the mouse anywhere on the
sheet you will see the cursor 'vibrating'. Could be annoying after awhile.

 

[Loulax07]

losing me here!

 

[Loulax07]

sorry that seems really daunting and have never done things like that before

 

[Logit]

Thats ok.

Here is a link to the updated project.

https://www.amazon.com/clouddrive/s...BRSQps1MeQ1iMZIhKI?ref_=cd_ph_share_link_copy

 

[Loulax07]

wow, very cool thank you so much! now i just need to figure out how to copy all this to my master spreadsheet. any tips or it's relatively easy?

 

[Logit]

These instruction may seem long and daunting ... but ... I've written them step by step so as to avoid confusion.
You can create a new workbook that is empty, and practice on it first if that would give you a higher comfort level
for the "real thing".

Right click the Sheet Tab at the bottom of your screen.

Click View Code and the Visual Basic Editor window (VBE) will appear

In the menu selections up top, click Insert / Module

The MODULE name will appear in the hierarchy tree located on the left side of the VBE

Double click the MODULE

On the right side is a larger window, all white in color.

Copy and paste the code into this window

Option Explicit
 
Sub ChangeColor()
Dim lRow
Dim MR
Dim cell
Dim OffSet
lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
    If cell.Interior.ColorIndex = 6 Then
        cell.OffSet(, 4) = "DUE": cell.OffSet(, 5) = cell.OffSet(, 2)
    Else
        cell.OffSet(, 4) = "": cell.OffSet(, 5) = ""
    End If
Next
End Sub

Close the VBE by clicking the X in the Upper Right Corner

Click the DEVELOPER tab up top on the menu selections

Click INSERT / a small form appears with many controls. Hover the cursor over these
controls until you find BUTTON. On my system it’s the upper left corner of the small form.
Click the control once.

Move your cursor to an area on your worksheet where you want the button to appear. Left click and
the button will be 'pasted' to your worksheet. At the same time, another form will appear showing
all the macros contained in your workbook. Find the macro named ChangeColor, left click it, then
click OK. You have now assigned that button control to that macro.

Anytime you click the button, the macro will run.

You can edit the text on the button and change its size by right clicking the button once. The button
will be surrounded with resizing controls. You can resize the button by left clicking/hold on one of the small
dots and dragging the mouse.

You can edit the text by moving the cursor over the resizing controls until the cursor turns into
four small arrows. When you see the arrows, right click and select EDIT TEXT. Pressing the DELETE key
will remove the text - you can then type any text you want.

You can move the button by hovering the cursor over the resizing controls until you see the four arrows.
Left click the resizing boundary, hold the left mouse button down and drag the mouse where you want
the button to be located.

Easy peezy ?