If you are familiar with macros you could put the following code in a standard VBA module:
Option Explicit
Public Const AMOUNT_CELL As String = "E2"
Const INVOICE_AMOUNT_COLUMN As String = "C"
Const MARKER_COLUMN As String = "D"
Public Sub LocateMatchingInvoices()
If Not IsNumeric(Range(AMOUNT_CELL).Value) Then Exit Sub
Dim invAmount As Currency
invAmount = Range(AMOUNT_CELL).Value
Dim nInvoices() As Long
ReDim nInvoices(0)
Dim totalAmount As Currency
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
'Unmark marker column
ActiveSheet.UsedRange.Columns(MARKER_COLUMN).Interior.ColorIndex = xlNone
Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(INVOICE_AMOUNT_COLUMN).Cells
If IsNumeric(rCell.Value) Then
totalAmount = rCell.Value
If totalAmount = invAmount Then
ActiveSheet.Cells(rCell.Row, MARKER_COLUMN).Interior.Color = vbRed
If MsgBox("Do you want to look further?", vbYesNo) = vbNo Then Exit Sub
End If
If totalAmount < invAmount Then
ReDim Preserve nInvoices(UBound(nInvoices, 1) + 1)
nInvoices(UBound(nInvoices)) = rCell.Row
Dim irow As Integer
irow = 1
Do
If totalAmount + rCell.Offset(irow).Value <= invAmount Then
totalAmount = totalAmount + rCell.Offset(irow).Value
ReDim Preserve nInvoices(UBound(nInvoices, 1) + 1)
nInvoices(UBound(nInvoices)) = rCell.Offset(irow).Row
End If
If totalAmount = invAmount Then
Call MarkInvoices(nInvoices)
If MsgBox("Do you want to look further?", vbYesNo) = vbNo Then Exit Sub
ReDim nInvoices(0)
ActiveSheet.UsedRange.Columns(MARKER_COLUMN).Interior.ColorIndex = xlNone
Exit Do
End If
If totalAmount > invAmount Then
ReDim nInvoices(0)
ActiveSheet.UsedRange.Columns(MARKER_COLUMN).Interior.ColorIndex = xlNone
Exit Do
End If
irow = irow + 1
If rCell.Offset(irow).Row > lastRow Then Exit Do
Loop
End If
End If
Next rCell
End Sub
Sub MarkInvoices(invoiceRows() As Long)
Dim i As Long
For i = 1 To UBound(invoiceRows, 1)
ActiveSheet.Cells(invoiceRows(i), MARKER_COLUMN).Interior.Color = vbRed
Next i
End Sub
And the following code in the VBA module of the worksheet you would like to exert this behavior:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(AMOUNT_CELL)) Is Nothing Then Exit Sub
Call LocateMatchingInvoices
End Sub
Now make sure that you adjust the constants to meet your requirements (the MARKER COLUMN is the column used to highlight the matching invoices). If you now enter an amount in the cell AMOUNT_CELL the code will mark all invoices adding up to the entered amount.
Please contact me if you are not familiar with macros.
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
Daryl S wrote:
Find cells in a range that sum to an amount
17-Nov-09
I have a spreadsheet of invoices with invoice numbers and amounts fo
clients. Sometimes I get a check with an amount for some of the invoices
but I do not know which invoices the check goes with. Is there a function o
add-in that can look at all the invoice amounts and find one or more that ad
up to the check amount
I realize there would be cases where more than one answer would result, bu
I would be happy to get any answer that works
Is there an add-in or other code to help me do this?
Thank you for any help on this!
--
Daryl S
Previous Posts In This Thread:
EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx