Solved

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

Posted on 2011-03-17
7
236 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Chris12090
  • 3
  • 2
7 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35157872
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
 

Author Comment

by:Chris12090
ID: 35158328
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
 
LVL 19

Expert Comment

by:akoster
ID: 35164192
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Chris12090
ID: 35164455
Here is the sample workbook.  I appreciate the help. Sample.xlsx
0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 35164768
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
 
LVL 24

Expert Comment

by:broomee9
ID: 35821671
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question