Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Pasting between workbooks

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Charlotte, NC
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to update a cell in worksheet A based on a value in worksheet B? Maybe I can explain it better. I have a worksheet ("Worksheet 1") with columns A thru D for example that I send to end users. They add a columm E and insert comments to explain certain rows of data. Two days later I'll refresh the information and send them a new worksheet ("Worksheet 2") with columns A - D.
    Is it possible to update column E on "Worksheet 2" with values from column E on "Worksheet 1" where column A in "Worksheet 2" matches column A in "Worksheet 1"? I hope this makes sense.

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure if this is what you want but...

    In worksheet 2 in a cell enter:

    =worksheet1!E1 and copy and paste down however many rows

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian and aferree:

    I believe when aferree is talking about Worksheet1 and Worksheet2, these are not worksheets from the same workbook, from what I can understand from aferree's statements, these are from two different workbooks. So, if that is the case reference to the workbook may have to be added to the linking formulas.
    aferree, please do post if the worksheets are from two different workbooks.

    Regards!

    Yogi Anand

  4. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha aferre,

    If it's two separate workbooks enter in a cell:

    ='[workbookname.xls]worksheet'1!$E$1

    Yogi..is this correct?

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Charlotte, NC
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, it's two different workbooks. What I'm trying to replicate is an Update query in Access (if that makes any sense)... Set E2 = E1 where A1=A2. E2 in this case would represent E column in "workbook B" and E1 would represent E column in "workbook A".I don't think I can set a reference to the cell the worksheet from "workbook A" b/c the worksheet in "workbook B" could have more or less rows.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 19:09, Brian from Maui wrote:
    Aloha aferre,

    If it's two separate workbooks enter in a cell:

    ='[workbookname.xls]worksheet'1!$E$1

    Yogi..is this correct?
    Hi Brian:
    It will be
    =[workbookname.xls]worksheet1!$E$1

    Regards!

    Yogi Anand

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi !

    I don't like those $ signs



    Also, I think the poster requires those links *if* a match on the respective columns "A" occurs, so we may need to present it something along the lines of :

    =IF(A1='[Worksheet 1.xls]Sheet1'!A1,'[Worksheet 1.xls]Sheet1'!E1,"something else")

    entered into worksheet 2, cell E1

    with no anchoring dollar signs so we can copy down

    whaddya reckon ?


  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris:
    I believe you are right -- you have picked up all the pieces -- my post was limited to pointing out to Brian that when the OP mentioned Worksheet1, and Worksheet2, these were not sheets of a workbook, but were indeed two different workbooks -- so I commented on the syntax of the linking formulation. Thanks for a close look at the whole issue.

    Regards!

    Yogi Anand

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ahhh yes, I see that now...



    Can you help with the last sentence of the posters last message ? it's confusing me a bit....

    (usually when something doesn't quite make sense, it means the whole orientation of the question is totally different from what you were interpretting it as !)

  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    Charlotte, NC
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Chris,
    That worked perfectly. Thanks. One follow-up question. Right now the formula looks for matches from column A1 in active worksheet to column A1 in the worksheet 1 and inserts the information from column E1. Is there anyway to have it search the entire column for a match and insert the respective information from the E cell in the same row.

    Just say for example the match for column A1 of active worksheet was actually in A4 of worksheet 1.

    This is what I meant by the active worksheet could have more or less rows than worksheet 1.

    Thanks in advance.

    [ This Message was edited by: aferree on 2002-05-02 13:38 ]

    [ This Message was edited by: aferree on 2002-05-02 13:40 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •