Solved

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

Posted on 2011-03-17
7
235 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

808 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