Find in multiple sheets copy in 1 sheet

Herman

New Member
Joined
Feb 28, 2002
Messages
28
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top