1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Update selected columns from one spreadsheet to another

Discussion in 'Business Applications' started by homevestors, Oct 24, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. homevestors

    homevestors Thread Starter

    Joined:
    Oct 24, 2004
    Messages:
    3
    Hello,

    I have two spreadsheets saved as seperate files with different names. One is sheet is called parcels.xls. The other is Parcels.Sales.Update.xls.

    parcels.xls is a master record of all properties in the county I live in. Parcels.Sales.Update.xls is a record of updated sales data for the properties in parcels.xls.

    parcels.xls has a unique PIN (property identifier number) for every property on the sheet. Parcels.Sales.Update.xls lists only PIN's that have recently sold. Both sheets have a column called SALES_YY (last date the proerty sold) and a column called SALE_PRICE.

    I want to take all of the PIN's from the Parcels.Sales.Update.xls sheet that match the PIN's in the parcels.xls sheet and update the SALES_YY and SALE_PRICE columns.

    Can anyone help me with this? Be easy on me, I am not a speadsheet expert.

    Thanks,

    Rob Caldwell
    Savannah, Ga
     
  2. dsovic

    dsovic

    Joined:
    Aug 19, 2004
    Messages:
    32
    Not a problem: VLOOKUP function does the job. Open both documents, position Yourself on document where You want results, press "=" and a list of functions will show. Choose VLOOKUP, a wizard will start. First empty place: mark cell with PIN
    Click to second empty place, switch to previous document and mark area holding both PIN's AND values (PIN's must be the first column!)
    Return to first document and in third E.Place write number of column containing values (like: if PIN's are in column A and You've select columns A,B and C and values are in column C, then You will enter number 3 as third column)
    Finally, FALSE as last value and that's it. There is my YIM if You need help.
     
  3. homevestors

    homevestors Thread Starter

    Joined:
    Oct 24, 2004
    Messages:
    3
    (I have set up both sheets so that the first three columns are named A= PIN, B= SALE_YY, C= SALE_PRICE)

    I do not believe I did this right as I did not get anyhting.

    Here is what I did:

    With both sheets open, I want to open parcels.xls and click on column A so that the entire column is highlighted. I select "=" and "VLOOKUP" and type in "PIN". I then move to the Parcels.Sales.Update.xls sheet and highlight columns A, B, and C for the TABLE_ARRAY field. I return to parcels.xls and type in 3 in the COL_INDEX_NUM field. And, finally, type in "FALSE" in the "RANGE_LOOKUP" field.

    Thanks for your help!!! :D
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Parcels.Sales.Update.xls lists only PIN's that have recently sold.

    In that case you might like to try another tack. Say columns A : D in parcels are headed "Property", "PIN", "SALES_YY", "SALE_PRICE".

    1. Select A1, choose AutoFilter from the Data menu.

    2. Click the dropdown in C1 and select Custom.

    3. Change the "equals" dropdown to "is greater than or equal to" ; type required date in the dropdown to the left & click OK.

    4. Back in the sheet, select columns B : D and copy. You can paste this where you like, it'll give you just the records where Sales_YY >= the date you typed in.

    Rgds,
    Andy
     
  5. johnske

    johnske

    Joined:
    Jun 23, 2004
    Messages:
    167
    Hi homevestors,

    Another way of doing what you want is to paste links between the two sheets:

    Go to the sheet you want the data to come from, select the cell (or cells) that have the data you want, then right-click and select Copy.

    Now, go to the other sheet where you want the data to be seen or used and select a cell where you want the data.

    Right-click and select Paste Special and then click Paste Link.

    John :p
     
  6. dsovic

    dsovic

    Joined:
    Aug 19, 2004
    Messages:
    32
    Let's start from beginning:
    - in "parcels.xls" You have PIN, SALE_YY and SALE_PRICE with unique PIN
    - in "Parcels.Sales.Update.xls" are actual values including PIN
    Question: does "Parcels.Sales.Update.xls" contains some PINs that are same?
    If so, do following:
    - in "parcels.xls" click in first cell in SALE_YY
    - type "=", choose "SUMIF" from function list
    - first option is criteria range: choose PIN column (A) from "Parcels.Sales.Update.xls"
    - second option is criteria to meet: click on first cell in PIN column in "parcels.xls"; disclaimer: that column must have values
    - Third option is column to sum: choose B column from "Parcels.Sales.Update.xls"
    - Your formula should look like: =sumif(Parcels.Sales.Update.xls!A:A,A1,Parcels.Sales.Update.xls!B:B) or alike
    - now do the same for first cell in SALE_PRICE, only sum by Parcels.Sales.Update.xls!C:C
    - mark both cells, "Copy", mark all cells in SALE_YY until last PIN, "Paste"
    - wait until calculation is done and that's it
    I hope that'll help.
     
  7. homevestors

    homevestors Thread Starter

    Joined:
    Oct 24, 2004
    Messages:
    3
    dsovic: When you say "choose PIN column (A) from...." Are you saying I should select the "A" which highlights the entire column, or should I click in the first field in column A, or do I just type in "PIN" in the criteria range. Also, the first fields in all of my columns are the names of the columns, i.e. "PIN", SALE_YY, and SALE_PRICE. When you say I should click on the first cell in SALE_YY should I actually click on the second because the first is the name?

    Thanks for your help.

     
  8. dsovic

    dsovic

    Joined:
    Aug 19, 2004
    Messages:
    32
    "A", a letter that indicates column (I mean entire column). The same as "1,2,3,..." indicates rows.

    That's right.

    I wasn't sure how Your spreadsheet looks like (now I'm pretty sure :)) and that caused ambiguousness in my description
    You're welcome. I'm glad to help if I can.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/288358

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice