Need assistance creating more complex data verification!

[Brent Cochran]

Update:

I have attached a link to be able to download the 2 files needed to accomplish what I'm looking for.

Using the R&M form, when you pull the data out of the GL Detail file, I would like it to also verify invoices on R&M to the GL by comparing the account code and total invoice value.  If these 2 match, then a YES would be inserted into the checkbox for GL.  If either the account code, or the total doesn't match, then a NO would be put into the GL box.   The invoice number should 99.99% of the time be correct, but if not, then we would just manually verify anyways.  I'm just trying to speed the process up drastically.

LINK: https://www.sendspace.com/filegroup/NAfQmgFxSC7KvmTbQHv%2BgQ

Attached is a accounting workbook that me and a fellow friend have been creating.   We have come to a point that we need the macro to verify information in the page with a file that is opened.  We currently have it set to pull data and calculate it, but comparing it needs to scan pertinent information and apply an answer to a column we are a little stumped.

When it opens the file needed, it needs to compare the invoice number to what is in the file it opens, once it finds that invoice number, it needs to verify the account code is correct (matches whats in the form, and verify the amount is correct ($)).  Trying to see if someone out there can assist with this idea.

Attached is the current form we are using so you can see what it currently does.  I can also attach a file for it to pull the data out if necessary.

Thanks for all the help.

'Open P&L File, and Find Account Code Sum Values from P&L
    Sub Btn_PL_Totals()

        'Unprotect Worksheet
            ActiveSheet.Unprotect conPass
        'Save SharedShared Location
            SharedShared = "\\NT-1\Shared\Shared"
        'Save Current Workbook, and Worksheet
            Set wbCurrent = Application.ActiveWorkbook
            CurrentMonth = ActiveSheet.Name
        'Handle Error Resultant from GL Sheet Being Already Open on Macro Run
            On Error GoTo PLOpen
        'Open, and Set wbSource to GL File
            With Application.FileDialog(msoFileDialogOpen)
                .InitialFileName = SharedShared
                .FilterIndex = 3
                .Title = "Select the GL file you would like to use!"
                .ButtonName = "Select"
                .AllowMultiSelect = False
                .Show
                If .SelectedItems.Count = 0 Then
                    ActiveSheet.Protect conPass
                    Exit Sub                   'Avoid Error on Cancel
                End If
                Set wbSource = Workbooks.Open(Filename:=.SelectedItems(1))  'Set wbSource to GL File
            End With
        'Verify wbSource is Good
            If wbSource.Worksheets(1).Range("A1").Value <> "GL Detail" Or wbSource.Worksheets(1).Range("A2").Value <> "Center" Then
                'Display Error Message, and End Sub
                    strMTitle = _
                        "Error: Invalid File Choice"
                    strMPrompt = _
                        "The file you have chosen does not appear to be a P&L file.  Please confirm you are selecting the correct file!" & vbNewLine & _
                        vbNewLine & _
                        "If you are positive you are selecting the correct file, but are still receiving this error please contact either Michael Keller, or Brent Cochran for assistance."
                    MsgBox strMPrompt, vbOKOnly, strMTitle
                'Exit Sub After Displaying Error Message
                    Exit Sub
            End If
        'Input Formula into "In GL:" Column under Account Code Totals
            wbSourceName = wbSource.Name
            wbCurrent.Activate
            For Each cell In Range("X10:X29")
                AccntCode = cell.Offset(, -5).Value
                PL_Formula = "=sumif('[" & wbSourceName & "]PVT'!$A$5:$A$2000,""" & AccntCode & """,'[" & wbSourceName & "]Pvt'!$K$5:$K$2000)"
                cell.Formula = PL_Formula
                PL_Value = cell.Value
                cell.Value = PL_Value
                    'Format Indicator Arrows Based on R&M to GL Value Comparisons
                        If cell.Value > cell.Offset(, -1).Value Then
                            With cell
                                .Offset(, 1).Value = ChrW(8593)
                                .Offset(, 1).Font.Color = RGB(0, 200, 0)
                            End With
                        ElseIf cell.Value < cell.Offset(, -1).Value Then
                            With cell
                                .Offset(, 1).Value = ChrW(8595)
                                .Offset(, 1).Font.Color = RGB(255, 0, 0)
                            End With
                        Else
                            With cell
                                .Offset(, 1).Value = vbNullString
                                .Offset(, 1).Font.Color = RGB(0, 0, 0)
                            End With
                        End If
            Next
        'Reprotect Sheet
            ActiveSheet.Protect Password:=conPass, AllowFiltering:=True
    Exit Sub
    
PLOpen:
    'Display Error Message, and End Sub if GL File Already Open on Macro Run
        strMTitle = _
            "Error: File Already Open"
        strMPrompt = _
            "The file you have chosen appears to already be open, and you have (reasonably) decided against opening a duplicate.  Please close the desired GL file before trying to open inside the R&M form." & vbNewLine & _
            vbNewLine & _
            "If you are encountering this issue due to a reason other than the one listed, please contact either Michael Keller, or Brent Cochran for assistance."
        MsgBox strMPrompt, vbOKOnly, strMTitle

    End Sub

[#2]

I had to paste the MACRO on here as the file wouldn't upload. I can manual supply the files if needed. The above macro is pulling the totals for each of the account codes and totaling them. I need either an addition to this macro to can like I said invoice information and put a verification YES or NO in a certain cell, or a separate macro to add onto after this one.

[#3]

It may help to upload the workbook with non-sensitive data.  I can roughly see what your code is doing, but it would greatly help to see the data structure in each of the workbooks...

Also:

1. Is your form on an the worksheet or is it a UserForm

     a. If it's a userform then we need to know the name of the textbox or combobox

2. What column is the account code located on the worksheet?  Is it in a specific cell?

3. Where is the $ amount on the form?  What is the cell location or textbox name?

4. Anything else you that may be pertinent.

If you wrap the code in code tags it helps with readability:  The button that looks like this: </>

[#4]

Thank you for your response.   I will strip sensitive data out of both the main form, and the file we are pulling the data out of / want to verify data out of.

I will either upload them here, or upload them to a dropbox account with open access for editing.

Sincerely,

Brent Cochran

[#5]

R&M / GL Detail File

The above link will get you to the 2 files in question.

Hopefully this will help with finding a solution for what I'm looking for.