How to different views (sheets) from the same sheet in Excel?
I want to create 2 sheets in Excel where one is the mirror of the other but with different organization. More precisely, I have one sheet with three columns that organize items inside their categories and one column for their price, like this:
Category | SubCateg | Item | Price
----------------------------------
catA | | |
| subA1 | |
| | aaa | 80
| | bbb | 100
| subA2 | |
| | ccc | 90
catB | | |
| subB1 | |
| | ddd | 100
I want to create another sheet with the same data but ordered by price, like this:
Category | SubCateg | Item | Price
----------------------------------
catA | | |
| subA1 | |
| | bbb | 100
catB | | |
| subB1 | |
| | ddd | 100
catA | | |
| subA2 | |
| | ccc | 90
| subA1 | |
| | aaa | 80
It doesn't necessarily need to look like this, but this ilustrates what I want.
I can't just order by开发者_开发知识库 price because of the categories in different columns. I also don't want to do any macro/formulas to concatenate categories+items or something like this.
I tried to make a pivot chart but I couldn't make it work, also because of the many columns to describe one item. Am I doing something wrong in the pivot chart?
Is there a better way to represent items inside categories so I could make this work better?
I also would like to make the changes made on the mirror sheet to be applied on the first, if I'm not asking too much...
Any way to make this work would be appreciated.
A better way to represent your data would be
catA SubA1 aaa 80
catA subA1 bbb 100
catA subB1 ccc 90
catB subB1 ddd 100
then you can sort the data, use it in a pivot table etc etc
From the tool bar DATA>SORT will so this for you.
Now to get the same sheet to display both views you can either write some VBA to set the sort criteria, or you can record a couple of macros and tack them onto a command button.
精彩评论