로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
How can I search and display relevent info from multiple sheets onto new sheet

답변됨 How can I search and display relevent info from multiple sheets onto new sheet

  • 2012년 8월 15일 수요일 오후 8:45
     
     
    로그인하여 투표
    0
    I'm using Win XP and Excel 2007. I'm curious if there is a formula(s) that will allow me to search a specific column across all sheets in a workbook (it is the same column on all the sheets) for a specific number and when it finds that number display all information in that specific row. So say I want to search column B of all 50 worksheets for the number 2 and display all the info from rows that have a 2 in the column on worksheet 1 how would I do this? I've tried using IF() and LOOKUP() but they gave me reference errors. Thank you in advance for any input.
     

모든 응답

  • 2012년 8월 16일 목요일 오전 4:54
     
     
    로그인하여 투표
    0

    check this link this may help you out

    http://www.teachexcel.com/excel-help/excel-how-to.php?i=35961

     
  • 2012년 8월 16일 목요일 오전 8:22
     
     답변됨
    로그인하여 투표
    0

    If the strutcure is same then use data consolidation feature of excel (From Data->Data Tools->Consolidate).By this you will be able yo get a consolidated sheet  on which you can apply Lookup.

    This feature will allow you to get a latest data if you linked by "crreate liks to source data"

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

     
  • 2012년 8월 16일 목요일 오전 9:58
    중재자
     
     답변됨
    로그인하여 투표
    0

    Hi,

    I think it can be done with vba code. Use For...Next to loop for each sheet to search.

    If you want to do it with macro, please give me more information about your workbook. You can share the workbook on https://skydrive.live.com/(remove the sensitive information) or just give me the screen shot about the columns, then I will write the code for you.

    Jaynet Zhang

    TechNet Community Support