none
Excel formula to copy paste a content from different sheet RRS feed

  • Question

  • Dears,
    I have 2 sheets in a workbook viz; Sheet1 and Sheet2, i need an excel formula to choose the price from Sheet2 when i input a code like 010001, 010900, 011500, 020100, 030200 etc in Sheet1 column "U"
    There are at least 5000 cells in Sheet2 with price and the corresponding codes eg: Sheet2 - column A has codes as shown above, Column C has its related price, so whenever i put a code in Sheet1 Column U, the formula should search the price in Sheet2 and paste it in Sheet1 against the same code mentioned in Sheet1 in Column Y. Also the value that is copied and pasted in column Y should multiply first with the units in column "X" before displaying in column Y.
    My file is attached for your reference.
    Thank you in advance,
    Ryz.
    Sunday, January 21, 2018 10:21 AM

Answers

  • For example in Y2:

    =IFERROR(X2*VLOOKUP(U2, 'Sheet2'!$A$2:$C$6000, 3, FALSE),"")

    Adjust the sheet name and the range if needed, then fill down.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Monday, January 22, 2018 5:16 AM
    • Marked as answer by Raaj306 Monday, January 22, 2018 10:37 AM
    Sunday, January 21, 2018 11:05 AM
  • I don't see any uploaded sample, but I think that the following version will do what you want:

    =IFERROR(X2*VLOOKUP(U2&"", Sheet2!$A$2:$C$6000, 3, FALSE),"")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Raaj306 Wednesday, January 24, 2018 8:26 AM
    Tuesday, January 23, 2018 5:48 PM
  • Hello Raaj306,

    You could try to format U2 In the formula.

    =IFERROR(X2*VLOOKUP(TEXT(U2,"000000"), Sheet2!$A$2:$C$6000, 3, FALSE),"")

    Since your original issue has been resolved, we would suggest you post new thread if you have any further requirement.

    Besides, this forum is for development issues related to Excel Object Mode. For issue related to formula, I would suggest you post threads on Excel IT Pro Discussions.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Raaj306 Wednesday, January 24, 2018 8:28 AM
    Wednesday, January 24, 2018 7:26 AM

All replies

  • For example in Y2:

    =IFERROR(X2*VLOOKUP(U2, 'Sheet2'!$A$2:$C$6000, 3, FALSE),"")

    Adjust the sheet name and the range if needed, then fill down.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Terry Xu - MSFT Monday, January 22, 2018 5:16 AM
    • Marked as answer by Raaj306 Monday, January 22, 2018 10:37 AM
    Sunday, January 21, 2018 11:05 AM
  • Simple and Best, thats how I can describe it Mr. Hans

    that was really Superb formula.

    Thanks in tons

    Regards, Raaj.

    Sunday, January 21, 2018 1:51 PM
  • Hi Raaj306,

    I'm glad to hear that your issue has been resolved . I would suggest you mark helpful reply as answer so people who runs into the same issue could find the solution efficiently. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 5:19 AM
  • Dear Mr. Hans,

    further to the above formula that you provided, pls i need some changes i.e sometimes i also type numeric values directly in Column "U" but not as "TEXT", so whenever i input codes manually i need the price shown in Column "Y" in sheet1 taken from sheet2.

    Thank you once again.

    Raaj.

    Tuesday, January 23, 2018 8:54 AM
  • Do you mean that you enter a price directly in U2? If so:

    =IF(ISNUMBER(U2),X2*U2,IFERROR(X2*VLOOKUP(U2, Sheet2!$A$2:$C$6000, 3, FALSE),""))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 23, 2018 11:47 AM
  • No Sir,

    sometimes[and most of the time the first forumla you gave is applied] i put the code like 500001 etc in column U manually, so the formula shd search for the price of that code in sheet2 and multiply with unit in column X and display the amount in column Y in sheet1.  

    hope i made it clear for you Sir.

    thanks,

    Raaj.

    Tuesday, January 23, 2018 12:19 PM
  • The original formula should already do that. What exactly is the problem?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 23, 2018 3:37 PM
  • Sir, the problem is Column U has formulas as =TEXT(S2+T2,"000000"), you can take a look in the sample i uploaded above and hence whenever i type manually a number like 500002, the result is blank.

    column U is both formula as well as manual, so i need that formula above to be modified to include manual entry also.

    thank you Sir.

    Raaj.

    Tuesday, January 23, 2018 3:56 PM
  • I don't see any uploaded sample, but I think that the following version will do what you want:

    =IFERROR(X2*VLOOKUP(U2&"", Sheet2!$A$2:$C$6000, 3, FALSE),"")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Raaj306 Wednesday, January 24, 2018 8:26 AM
    Tuesday, January 23, 2018 5:48 PM
  • Hello Raaj306,

    You could try to format U2 In the formula.

    =IFERROR(X2*VLOOKUP(TEXT(U2,"000000"), Sheet2!$A$2:$C$6000, 3, FALSE),"")

    Since your original issue has been resolved, we would suggest you post new thread if you have any further requirement.

    Besides, this forum is for development issues related to Excel Object Mode. For issue related to formula, I would suggest you post threads on Excel IT Pro Discussions.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Raaj306 Wednesday, January 24, 2018 8:28 AM
    Wednesday, January 24, 2018 7:26 AM
  • GR8!!!!!

    Mr. Hans, that one has done a lot of relief for me with my office work.

    Thanks in tons.

    with respect,

    Raaj

    Wednesday, January 24, 2018 8:28 AM
  • Hi Mr. Terry,

    Your formula has also the same effect as is Mr. Hans'

    thank you very much for your help, God Bless.

    with regards,

    Raaj.

    Wednesday, January 24, 2018 8:29 AM