+ Reply to Thread
Results 1 to 3 of 3

When a value is selected from a drop down list, data is copied from sheet2

  1. #1
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Talking When a value is selected from a drop down list, data is copied from sheet2

    Good afternoon all! This is my first post so am hoping it will get some attention and a solution.
    To keep it as simple as possible my spreadsheet has two sheets to it. Sheet2 contains a list of items, along with product codes, descriptions and costs, Sheet1 is my main page which I will be collating all of the correct items on to do further calculations on later.

    sheet2 as shown below:

    Sheet1.png

    Which is a stock list of items, which for now will remain as is and not be updated.

    Sheet1 Row A has the same headers as sheet 2. Column A has a validated data list taking the product code from sheet2 as shown:

    Sheet2.png

    My question is: If I select a product code into cell A2 on sheet1 from the drop down list, is there a formula that would automatically insert the associated data from sheet2 cells B2,C2,D2 into cells B2,C2,D2 on Sheet1? As shown:

    Sheet1 ideal.png

    Many Thanks in advance for any input you may give.
    Alan

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,065

    Re: When a value is selected from a drop down list, data is copied from sheet2

    Try

    in B2
    =INDEX(Sheet2!$A$2:$D$12,MATCH($A2,Sheet2!$A$2:A$12,0),2)
    in C2
    =INDEX(Sheet2!$A$2:$D$12,MATCH($A2,Sheet2!$A$2:A$12,0),3)
    in D2
    =INDEX(Sheet2!$A$2:$D$12,MATCH($A2,Sheet2!$A$2:A$12,0),4)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Re: When a value is selected from a drop down list, data is copied from sheet2

    Special-K,
    That's great, thank you for the quick reply. Works as it should!! Thank you.
    As I am looking at setting up sheet1 with the formulas in, ready for input is it possible to show nothing in the cells until an option from column A is chosen rather than the #N/A:

    Error message.png

    I am guessing its "" in there somewhere?
    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] insert copied data from sheet1 to sheet2
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2014, 10:21 AM
  2. Replies: 2
    Last Post: 06-29-2012, 10:29 AM
  3. Extract data from sheet selected from drop down list
    By kanight in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2011, 07:20 AM
  4. dde data copied from sheet1 to sheet2
    By chuckb74 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-02-2009, 03:53 PM
  5. Replies: 1
    Last Post: 04-11-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1