

 Thread Tools  Search this Thread 
#1
 
 
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  BWhat 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 : 27112010 at 10:54 PM. 
#2
 
 
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)+1ROW(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
 
 
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
 
 
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. =IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$20=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet1!$A$1:$A$30,$E$1)+1ROW(A1))),"") 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
 
 
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: Code: 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 

Tags: excel, excel worksheet, formula, macro, ms office, vba code 
Thread Tools  Search this Thread 

Similar Threads for: "Import data from one excel worksheet to another"  
Thread  Thread Starter  Forum  Replies  Last Post 
Excel 2007 Data Import via Bloomberg  YajasK  MS Office Support  2  09022012 06:24 PM 
Clicking on Worksheet on multiple Worksheets in the single Excel file not allow viewing the worksheet in Microsoft Excel 2010  Dipanwita  Windows Software  8  04122011 10:24 AM 
How to Import all text data in Excel spreadsheet  racer  Tips & Tweaks  1  06012010 09:38 PM 
How do i import Excel data from password protected site  Axton  Networking & Security  3  21102009 05:10 PM 
Import data from excel to MPP and set to outline levels  manishdk  Microsoft Project  4  15092008 11:40 PM 