Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Find in multiple sheets copy in 1 sheet

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a little problem, i have 5 worksheets with data column a is always a article number
    column b and c are used for descriptions i want to have a function in sheet6 which looks in all 5 sheets if the number i put in cell a1 on sheet6 is found so if the number is found in sheet1 the row where the number is is copied over to sheet6, if the number is also in sheet3 this row is also copied to sheet 6. when the search is done i have in sheet 6 all rows where the number in column a is the number i searched for.
    how can i do this

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    USING EQUATIONS:

    Spreadsheet:
    Article File&rangeToSearchIn description
    Q123W File1 =Vlookup
    Q123W File2 =Vlookup
    Q456W File1 =Vlookup
    Q456W File2 =Vlookup

    First step: To retreive data from the other sheets, you'll use a vlookup function. {If there is a chance that columns could be inserted in the otherworksheets, then for the column number in the lookup you should use the match function to look for the column with the correct title.}

    Second step:
    If you don't expect to add sheets, then simply copy the formula down five rows, repeating the article search for five times. Since the information is in separate files, you'll need to enter the file name in each individual eqn.

    Third Step:
    Us an autofilter to hide all the lines that return #n/a

    Or third step:
    Use array formulas in a 7th sheet to show only those rows that do not have #N/A . Search this board, MrExcel & j-walk.com for "unique list" and you should find a variety of similar equations.

    Note: Whe I was using vlookups to other files excel would crash if I answered yes to the question "do you want to update links" that you get when you open the file. I answer "no", open the linked files, then press CTRL-ALT-F9 to force complete recalculation, ensuring that everything is updated.

    The problem with this is that the users of the other spreadsheets can;t use it while I have them all open. So sometimes I sue links to "import" the data from each other workbook on to a sheet in my workbook. Therefore I only have the others open momentarily. You can then use INDIRECT Function to go to the correct sheet, rather than hard coding each eqn to look in one file.

    METHOD #2:
    Use VBA.

    Cheers

    Brian

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think it can be achieved with 1 basic DGET formula placed in 10 cells.

    Note: ( for this example I'm assumming your sheet1-5 have the following headings : RefNum,Comment1,Comment2

    1- Create a 6th sheet to view summary data

    2- in Sheet6!A1 place subject heading like "RefNum", sheet6!B1 = Comment1, sheet6!C1=Comment2

    3 - In Sheet6!B2 place formula =DGET(Sheet1!A:C,"Comment1",$A$1:$A$2)

    4 - in Sheet6!C2 place formula =DGET(Sheet1!A:C,"Comment2",$A$1:$A$2)

    5 - repeat 3&4 in cells B2 to C6 needing only to change Sheet1 to 2,3,4,5

    DONE: now all you need to do is type the RefNum you want info on in cell A2 and cells B2 to C7 will autofill





    [ This Message was edited by: Nimrod on 2002-05-06 22:06 ]

    [ This Message was edited by: Nimrod on 2002-05-06 22:08 ]

    [ This Message was edited by: Nimrod on 2002-05-06 22:09 ]

    [ This Message was edited by: Nimrod on 2002-05-06 22:13 ]

    [ This Message was edited by: Nimrod on 2002-05-06 22:14 ]

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to have cells appear blank when no data is found on a particular RefNum then change the formulas to the following:
    =if(iserror(DGET(Sheet1!A:C,"Comment2",$A$1:$A$2)),"",DGET(Sheet1!A:C,"Comment2",$A$1:$A$2))

    I.E. If an error occurs from formula then put "" in cell if no error then place value.

    [ This Message was edited by: Nimrod on 2002-05-06 23:44 ]

    [ This Message was edited by: Nimrod on 2002-05-06 23:45 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx i am going to try the different approaches.
    Greetings Herman

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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