Pulling all occurances?

[LauraAdmin]

Hello, all.

I am looking to do two things to an Excel spreadsheet:

The first thing I'd like to do is to program my worksheet to pull and display complete rows of information from another worksheet if column C matches a certain codeword. There will be about 200 instances of this codeword in column C in a sheet of about 1000 rows. How do I program Excel to pull all of those 200 instances and not just the first one?

The second thing I'd like to do (and I know it's a pretty basic task for Excel, but I don't know where to look for a tutorial on it, since I don't know what it's called) is to have my first task pulling information from another spreadsheet on the Network, and to automatically update that information when I open the file. I don't even know what this is called, so I'm having a hard time finding info on how to get it done.

Help is greatly appreciated on either topic!

-Laura

[olasa]

Advanced Autofilter is one option:
Menu: Data>Filter>Advance Filter...>

Tutorials:
Text: http://www.personal-computer-tutor.com/datafilters.htm
Video: http://www.datapigtechnologies.com/f...utofilter.html
--------------------

Another option is to use =VLOOKUP. But that requires that you have an Index list.
If you don't I would recommend that you add an index number in you 1000 row sheet. You can do this by adding a column in Column A and then add 1,2,3 and then drag down that area, Excel will automatically increase the number to 4,5,6....1000.
Then use Autofilter to copy and past the index numbers that match you selection to you new sheet.
.............
The next step is to learn how to use the =VLOOKUP function. Then use =VLOOKUP in your new sheet to pull the rows that you want.

Tutorials:
Video: http://www.datapigtechnologies.com/f...s/vlookup.html
Text: http://www.personal-computer-tutor.com/vlookup.htm
...............
The third thing is to make a smart formula that adds column 1,2,3,...20.
There is a function called =COLUMN(). If you type =COLUMN(C1) --> 3 since C is the third column. We can use this number to pull the column we want.

If we put this together:
=VLOOKUP(A$3,Sheet2!$A$3:$L$1000,COLUMN(A$2),0)
$A3: your filtered index numer list
Sheet2!$A$3:$L$1000 : your area
COLUMN(A$2) : the column number
0 : only take exact matches.

Copy this formula all over you target sheet

Hope this can be of use

Ola Sandström


There other ways but it involves using more complx formulas to create row index numbers based on the last record pulled..