Populating a table with filtered result of another table

[pikakathy]

Hi, I have a worksheet that has a large table that I filter by a certain
column. Is it possible to populate a table on a second worksheet that
is the filtered result of the first worksheet's table?

For instance, if I have a table listing types of cars and their color /
make / etc. I sort it to see only Red cars. Is it possible to have my
second worksheet populated with a table of red cars, and my third
worksheet populated with a table of green cars?

 

[Ron Coderre]

Yes! Here'a an example of how:

Assumptions:
Sheet1 contains the source list (with appropriate column headings)
Column Headings: Make, Model, Color, Other1, Other2, etc
Sheet2 is the destination for the filtered list.

Using Sheet2
A1: Color
A2: Red

C1: Make
D1: Model
etc..for the rest of the col headings

Select C1 through the last column heading you want returned
Data>Filter>Advanced filter>
Check: Copy to another location
List Range: (Switch to Sheet1 and select all of the data list)
Criteria Range: (Select Sheet2 range A1:A2
Copy to: Select the Sheet2 column headings beginning on C1
Click the [OK] button

That should return all of the referenced data for Red vehicles.

Does that help?

Regards,
Ron

 

[pikakathy]

Hi Ron,

Thanks for your help! I think I might be messing something up.
Here's my sheet 1:

A1 B1 C1 D1
name color make model
alison red toyota a
bobby red camry b
cathy blue lexus c
dana green camry a
frances yellow ford b
garrik yellow cadillac c
holly green mercedes a
inga blue cadillac a
jenn red toyota c

I want my sheet 2 to have the same column headings, but only list red
cars. I'm getting a little thrown off by the cell references.

Thanks!

 

[Ron Coderre]

Here's what I did on Sheet2:

A1: color
A2: red

C1: make
D1: model
E1: color

Select C1:E1
Data>Filter>Advanced Filter (If you get a warning...just click [OK])
Check: copy to another location
List Range: Sheet1!$A$1:$D$10
Criteria Range: $A$1:$A$2
Copy to: $C$1:$E$1
Click the [OK] button

Does that help?

Regards,
Ron

 

[pikakathy]

Thanks so much - that totally helped!

Now is it possible to do this dynamically? So that if I change a valu
of a color in sheet one it would reflect in Sheet 2

 

[Ron Coderre]

It won't be automatic, but it can be done with a bit of tweaking....

Select Sheet2
Insert>Names>Define
Names in Workbook: Sheet2!Database
Refers to: =Sheet1!$A:$D
Click the [OK] button

Select an empty cell on Sheet2
Data>Filter>Advanced filter>
Check: Copy to another location
List Range: (Press the [F3] key and select Database)
Criteria Range: (Should already be there as $A$1:$A$2)
Copy to: (Should already be selected as )
Click the [OK] button

The next time you run the Advanced Filter, you only need to do this:
Select an empty cell on Sheet2
Data>Filter>Advanced filter>
Check: Copy to another location
List Range: (Press the [F3] key and select Database)
Click the [OK] button

If that's still too much work...we can explore VBA options.

Does that help?

Regards,
Ron

 

[pikakathy]

That is so amazing. Thanks Ron!

In the future I'd definitely like to learn vba options, but I'm just a
beginning when it comes to that. Can you recommend a good online
tutorial to get started learning that?

Thanks again!!

 

[Ron Coderre]

There are so many on-line resources that I won't even pretend to have a
favorite. It's all a matter of personal preference. Here are just a
couple to get you started:

http://datapigtechnologies.com/ExcelMain.htm
(Free on-line video tutorials)

http://frontpage.et.byu.edu/ce270/vbaexcel_primer/intro.htm

Regards,
Ron