+ Reply to Thread
Results 1 to 16 of 16

Assaign data to cells from another worksheet where there is definition

  1. #1
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Assaign data to cells from another worksheet where there is definition

    Hello,

    I have one worksheet Definition where I have cca 200 countries and each country is assigned specific region code.
    WORKSHEET Definition - Example:

    Column CTR_CODE | Column Biz_reg
    AM EU
    AD EU
    BH ME
    etc.

    In the same workbook I have worksheet Modified with again CTR_CODE, and here comes the task: I need to assign Biz_reg to all cells according to CTR_CODE/Biz_reg in worksheet Definition
    WORKSHEET Modified - Before

    Column CTR_CODE | Column Biz_reg
    AM
    AD
    BH

    WORKSHEET Modified - Expected Result:
    Column CTR_CODE | Column Biz_reg
    DE US
    AD EU
    BH ME
    AM EU



    Please, could someone help?

    Jana
    Last edited by jbmd; 05-29-2017 at 10:39 AM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Hello Jana & Welcome to the Forum,

    In B2 on the Modified sheet, =VLOOKUP(A2,Definition!$A$2:$B$4,2,0)
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    I am afraid not. I improved my posting above.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Quote Originally Posted by jbmd View Post
    I am afraid not. I improved my posting above.
    I don't mind you changing your initial post, but don't say I'm afraid not. Based on your initial post I believe what I posted was accurate.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    The file is attached. I manually filled out a few cells in Biz_reg.
    In real sheet there is more than 2300 rows in Modified worksheet so doing it manually would be a disaster.


    Thank you.
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Modified sheet B2 copied down...

    =VLOOKUP(A2,Definition!A:C,3,0)

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,083

    Re: Assaign data to cells from another worksheet where there is definition

    Quote Originally Posted by jeffreybrown View Post
    I don't mind you changing your initial post, but don't say I'm afraid not.
    Nonetheless, the rules of the forum forbid it:

    Never edit a thread or post to which others have already responded.
    jbmd - please take a moment to read through the forum rules and abide by them at all times, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    forbid it
    Such strong language...

    abide by them at all times
    Or Else...

  9. #9
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    This formula is perfect. However since I am a novic, I do not know how to apply this formula to all 2330 rows in column B in Modified worksheet.

    I tried this:
    I checked (labeled) all 2330 rows in column B in Modified and tried =VLOOKUP(A2:A232,Definition!A:C,3,0) but it always gives me just first row.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Hi Jana,

    No, just use the formula posted in post #6 and then drag down.

    The Vlookup has four arguments with the first argument a single lookup value. In this case, A2

    =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    As you copy down, A2 will change to A3, A4, etc.

    You can even double click the formula down...

    https://support.office.com/en-us/art...2-35a236c5b5db

  11. #11
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    That also works perfect. Thank you.
    I am sending attached book2 where in real life I have a few columns in front of previous ones in Modified. How should I change formula?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    =VLOOKUP(D2,Definition!A:C,3,0) ?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,083

    Re: Assaign data to cells from another worksheet where there is definition

    Quote Originally Posted by jeffreybrown View Post
    Such strong language...



    Or Else...
    It makes for a very disjointed thread if people keep altering posts after the fact and it is explicitly mentioned in the rules that they should not do so, for good reason. You might not mind, having seen the original post, but for anyone joining the thread later on, it is nigh on impossible to follow. That's why forum members are asked not to do it.

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Hi Jana,

    The first few columns on the Modified sheet (A, B, and C) will have no impact on the formula.

    =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    lookup_value -- No matter how many columns before the lookup_value, the lookup value is D2

    table_array -- Your table_array is in the Definition sheet column A:C

    Important: For a Vlookup formula to work, the lookup value in the table array must be the farthest most left value of the table and the Vlookup function moves to the right to return a value

    col_index_num -- In the table_array, you want to return the result from the 3rd column, column C

    range_lookup -- Use 0 for an exact match

    http://www.contextures.com/xlFunctions02.html
    Last edited by jeffreybrown; 05-29-2017 at 01:24 PM.

  15. #15
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Assaign data to cells from another worksheet where there is definition

    Now I fully understand. And really thank you for explanation.

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,081

    Re: Assaign data to cells from another worksheet where there is definition

    Happy to hear you now have a working understanding of the Vlookup. You're welcome.

+ 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] Chart Series Data Definition Example?
    By endly in forum Excel General
    Replies: 2
    Last Post: 11-06-2015, 07:16 PM
  2. [SOLVED] Cells losing their outline definition
    By wodehouse9 in forum Excel General
    Replies: 4
    Last Post: 08-29-2013, 03:37 PM
  3. Array Definition - Different Data Types for each column
    By dax2ib in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2013, 08:53 AM
  4. Issue with User Form Data Definition
    By tiggerzen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2012, 04:07 AM
  5. Data-Definition Problem
    By bstubbs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2011, 06:33 PM
  6. Replies: 0
    Last Post: 03-08-2007, 02:25 AM
  7. [SOLVED] End of data definition
    By Surfstiling in forum Excel General
    Replies: 1
    Last Post: 03-24-2005, 09: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