Results 1 to 5 of 5

Thread: Import data from one excel worksheet to another

  1. #1
    Join Date
    Nov 2010

    Import data from one excel worksheet to another

    I require to import data from one excel worksheet to one more to exhibit all employee names from a exacting company.
    For example:
    A - B
    Amb - Kali
    Mart - Fred
    M-Mart - Abi
    Amb - Kali
    Mart - Fred
    M-Mart - Abi
    Amb - Kali
    Mart - Fred
    What formula can I make use of in another sheet that is in sheet2 to exhibit a list of all Amb employees from sheet1?
    Last edited by arjun rantu; 27-11-2010 at 10:54 PM.

  2. #2
    Join Date
    Nov 2008

    Re: Import data from one excel worksheet to another

    Attempt this ARRAY formula on sheet 2. Enter the formula and place your lookup value of Amb in E1. Drag the formula down until it start creating blanks.
    =IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$30=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet 1!$A$1:$A$30,$E$1)+1-ROW(A1))),"")
    This is an array method that must be entered by pressing CTRL+Shift+Enter and not only Enter. If you do it accurately in that case Excel will place curly brackets {} around the formula. You cannot type this manually. If you edit the formula you have to enter it once more with CTRL+Shift+Enter.

  3. #3
    Join Date
    Nov 2008

    Re: Import data from one excel worksheet to another

    I also want to do the same and don’t get any formula. I just see your post but I am not extremely higher. Can you please put in plain words to me what the dissimilar parts of the formula are? Obviously the data I am using here is not genuine. While I attempted adjusting the sheet or cell references to make use of in my genuine workbook it did not work. I think I will have an enhanced possibility if I know what I am working with.

  4. #4
    Join Date
    May 2008

    Re: Import data from one excel worksheet to another

    Just try the following formula.
    =IFERROR (formula,"")
    The external IFERROR merely makes it revisit blanks while it runs out of matches. Now in every case I will bold the bit of the formula. Here I am talking about the primary bold bit are the values we are annoying to depart. Sheet1 is the name of the sheet which is followed by a “!” and $B$1:$B$30 is the range.
    Now at this time 3 bits of the formula are underlined and this is the range which encloses the lookup values. This range A1:A30 can be any volume but have to be the similar size as the range B1:B30

  5. #5
    Join Date
    Nov 2008

    Re: Import data from one excel worksheet to another

    I advise by means of an easy macro in its place of a few strange formulas, since the latter needs manual interference in any case that is, predetermining the number of consequences or deciding how much times to copy down the formula. One such macro:
    Sub multiLookup()
    Dim sLookupTable As Range, sResultTable As Range, s As Range
    Dim l As Long
    Set sLookupTable = Worksheets ("sheet1").Range ("a1:a100")
    Set sResultTable = Worksheets ("sheet1").Range ("b1:b100")
    lookupValue = UCase(Selection)
    l = 0
    For every s In sLookupTable
       If lookupValue = UCase(s) Then
          l = l + 1
          Selection.Offset(l) = sResultTable.Cells(s.Row)
       End If
    Next r
    End Sub
    Place the names of the search for values keen on a row of cells in Sheet2, e.g. A1, B1, C1, and so on. Choose every lookup value, push alt+F8, and run the macro. The multiple results will come into view in the column beneath the lookup value.

Similar Threads

  1. Excel 2007 Data Import via Bloomberg
    By YajasK in forum MS Office Support
    Replies: 2
    Last Post: 09-02-2012, 06:24 PM
  2. Replies: 8
    Last Post: 04-12-2011, 10:24 AM
  3. How to Import all text data in Excel spreadsheet
    By racer in forum Tips & Tweaks
    Replies: 1
    Last Post: 06-01-2010, 09:38 PM
  4. How do i import Excel data from password protected site
    By Axton in forum Networking & Security
    Replies: 3
    Last Post: 21-10-2009, 05:10 PM
  5. Import data from excel to MPP and set to outline levels
    By manishdk in forum Microsoft Project
    Replies: 4
    Last Post: 15-09-2008, 11:40 PM

Posting Permissions

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