Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-17
7
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 19

Expert Comment

by:Arno Koster
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:Arno Koster
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

Accepted Solution

by:
Arno Koster earned 2000 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:Tracy
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

715 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