Auto Split to multiple sheets

[mkuter]

I have a question, essentially what I would like to do is separate certain fields by category and have them auto-populate separate sheets. I will use Apples as a way to explain a little clearer, I hope.

Apple 1 is Green
Apple 2 is Red
Apple 3 is Red
Apple 4 is Green
Apple 747 is Green
Apple 748 is Red
Apple 749 is Green
Apple 750 is Red

What I want to do is have one column that would be "Apple Name" followed by the next column which is "Apple Color". There would be alot more information after that as well in terms of the owner of the Apple and which stage it is currently in. I also have 3 sheets. One Master and one for each respective color.

On my master sheet I put Apple 1 in Column A, in Column B I have it setup as Data Validation List selecting between "Red" and "Green".

What I am trying to do is once I select whether it is a Red Apple or a Green Apple the info would automatically populate to the respective color. So for Apple 1 I select Green, I would like it and all the info to also show up on the "Green" sheet. So by the time I am done filling in the info for all 750 apples, they would separate themselves automatically.

Hope that makes sense and that someone could help.

Thanks,
Mike

 

[Smitty]

Welcome to the Board!

If you're keeping your "master" data in one worksheet anyway, then you can create a pivot table and put the color field in the pivot table filter field. Then you can use the Show Report Filter Pages option, which will create a new pivot table for each item listed. As you change/add data on the master sheet you just refresh the pivot tables to update them.

HTH,

 

[mkuter]

Thank You Smitty!

I thought about that, and forgive me because my experience with pivot tables is still pretty noobish, but my problem is all of the data that comes after the initial stuff. For example the status. It comes up as Sum of Status, with a numerical value, is there a way to actually have it say the actual status? So for Apple 4 if it is Half Eaten can I have it say that instead of a sum or a total? I see the options in the Value Field Settings but I don't see anything that seems like it would apply. Again, I am pretty noobish.

I should also mention this is Excel 2010, not sure if it makes a difference or not.

 

[Smitty]

Sure, if you want pretty much an exact representation of your current data without calculations just put the fields in the Row section instead of the Column section.

 

[mkuter]

Oh Duh...I had the Report Layout showing in compact form. Sometimes it is just so easy to overthink the easy. Much appreciated Smitty.