Replace cell value in one spreadsheet with cell value in another spreadsheet

I have a sheet called Master Census that holds a master census of our patients and then I have another sheet that holds addendums for the month for the patients.  Both sheets have columns Patient Name and Contract Amount.  The patient name column of the addendum sheet uses the named range Patient_Census from the Master Census page to create a validation drop down list.  

If the patient is on the addendums sheet I want to be able to copy/replace the cell value for Contract Amount on the master census sheet to the cell value for Contract Amount on the addendums sheet through use of a Macro for that patient.
Chris12090Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Arno KosterConnect With a Mentor Commented:
insert this macro
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range("button_update")) Is Nothing Then
        Cancel = True
                
        '-- initalise
        Set src = Worksheets("Addendums")
        Set dst = Worksheets("Master Census")
        
        '-- loop through addendums
        For Row = 2 To src.UsedRange.Rows.Count - 1
            add_name = src.Range("A" & Row)
            add_amount = src.Range("C" & Row)
            dst.Select
            Set result = dst.UsedRange.Columns(2).Find(what:=add_name, lookat:=xlWhole)
            If result Is Nothing Then
                MsgBox "Patient '" & add_name & "' not found on Master Census list !", vbExclamation + vbOKOnly, "Error on parsing addendum list"
            Else
                dst.Range("H" & result.Row) = dst.Range("H" & result.Row) + add_amount
            End If
        Next Row
        
        '-- finish
        Set src = Nothing
        Set dst = Nothing
    End If

End Sub

Open in new window


in the code section of Sheet1.

Then, pick any cell (eg. the "Total Census" cell A5), and give it the name "button_update"

when you now double-click on this particular cell, the values will be updated.
0
 
Arno KosterCommented:
you could use a lookup function :

On the addendum sheets in the cell for the contract amount you could place this formula :

=VLOOKUP([addendum patient name];[master columns for patient name and contract amount];2;False)

The 2 in this formula indicates that the second column in the 'master' range, containing the contract amount, is to be retrieved.
the false in this formula indicates that an exact match for patient name is required
0
 
Chris12090Author Commented:
I think I confused you with my explanation.  Let me try again.

An addendum can only occur for a patient that already has an amount for a contract.  That amount will be on the Master Census page.  When an addendum occurs the patient is entered on the addendum page signifying a change in their contract amount.  At the end of the month I want to be able to update the master census page with the new amounts for the patients with addendums.  I hope I was more clear this time.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Arno KosterCommented:
I see, this indeed is not the approach taken in my first comment.
When you want to update the master census contract amounts to add the addendum contract amounts for the differen patients, it would be very helpfull if you could post the workbook (if possible with a couple of dummy values, please post no actual data).
I could then add the macro you are looking for and we can elaborate on that.
0
 
Chris12090Author Commented:
Here is the sample workbook.  I appreciate the help. Sample.xlsx
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.