+ Reply to Thread
Results 1 to 6 of 6

Pulling data from one sheet to another in excel

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    6

    Pulling data from one sheet to another in excel

    On sheet 1 of my spreadsheet I have a column that needs to be populated with a number that corresponds with a country.

    At present users have to find the country they are inputting from a list on sheet 2, column A, and the corresponding number in sheet 2, column B. They then need to go back to sheet 1 and input the correct number. Is there a way in which users could type in the country in sheet 1, and using a formula, excel automatically changes it to the associated number in sheet 2? I do use Vlookup but was under the impression this could only be done once the spreadsheet was final and not in anticipation of data being entered.

    I hope this makes sense!

    Thanks

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Pulling data from one sheet to another in excel

    Will be better to upload a small sample workbook
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Pulling data from one sheet to another in excel

    I have attached an example sheet.

    I would like for a user to be able to type in a country e.g. Britain in sheet 1, column 2 and for excel to automatically change the country to the corresponding code e.g. 25 which can be found in sheet 2 column B.

    Thanks
    Example SS.xlsx

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Pulling data from one sheet to another in excel

    Do you mean in C2 of your first sheet and copy down?

    =IFERROR(INDEX('Sheet 2'!$A$2:$A$9,MATCH('Sheet 1'!B2,'Sheet 2'!$B$2:$B$9,0)),"Does not exist")

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Pulling data from one sheet to another in excel

    I was hoping it would overwrite the country name in B2 (as i don't actually need the country name) but if that is not possible then happy to create a new column for the code and then delete the column I don't need once the sheet is complete.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Pulling data from one sheet to another in excel

    You can not overwrite this using formula.

    Maybe using VBA but i am not able to help you in this case.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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