Find in multiple sheets copy in 1 sheet

[Herman]

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

[Brian]

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

[Nimrod]

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 ]

[Nimrod]

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 ]

[Herman]

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