Solved

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

Posted on 2011-03-17
7
232 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now