Excel... two sheets, same data, different views

[TridenT]

I have this sheet that I like to sort by the first column sometimes and I like to sort by the second column or third column the other time.

And I like to flip between these views a lot. However, I am also inserting new data into this spreadsheet all the time. So, my question is, how do I have 3 sheets with the 3 different sorted views that I want and then insert into one sheet so that it cascades over into the others properly?

Is this even possible? I hate having to right click and choose my sort method all the time. It would be nice to just click between sheets to get the data I want. Even have multiple sheets of the same data up at the same time but sorted differently.

Anyone know how? My Google-fu is not strong.

 

[edro]

Can you just point to the value on the previous sheet?

 

[JM Aggie08]

Reference cells, son.

 

[cronos]

Make multiple worksheets:
1. source (that you edit)
2. reference source, and sort #1
3. reference source, and sort #2
4. can add more if you want

Or...

Create multiple pivot tables out of the source data

 

[rcpratt]

Oh Trident. Yes, just reference the cells.

 

[Homerboy]

Reference cells.
Hell no to pivot tables. Horrible horrible pivot tables.

 

[TridenT]

I already have all this data, I don't want to manually reference each cell. Also, how will it know to reference the new data? By me copying a bunch of blank cells? That will create an issue when sorting later. (empty cells being sorted to be before others and so forth)

 

[minendo]

On each tab (except the main tab) just go to Data - Connections from Other Sources and select From Microsoft Query.

 

[CPA]

Reference cells.
Hell no to pivot tables. Horrible horrible pivot tables.

What's wrong with pivot tables? They have their place.

 

[MrPickins]

What's wrong with pivot tables? They have their place.

:thumbsup: They can be downright essential.

 

[AreaCode707]

I already have all this data, I don't want to manually reference each cell. Also, how will it know to reference the new data? By me copying a bunch of blank cells? That will create an issue when sorting later. (empty cells being sorted to be before others and so forth)

=if(cellRef="","",cellRef)

 

[rcpratt]

Nobody said manually reference. Just reference A1 and then copy that for your entire range.

And yes, use AreaCode's solution to handle blanks.

 

[Ophir]

Looking forward to the 200 post argument after Trident refuses to accept the solutions posted above.

 

[MrDudeMan]

:thumbsup: They can be downright essential.

Anyone who thinks pivot tables are worthless obviously has no clue how to use them. They are unbelievably useful.

 

[Tweak155]

Anyone who thinks pivot tables are worthless obviously has no clue how to use them. They are unbelievably useful.

I make my living working entirely in Excel, and I have no idea how to use a pivot table. But, I don't think they aren't useful either.

 

[MrDudeMan]

I make my living working entirely in Excel, and I have no idea how to use a pivot table. But, I don't think they aren't useful either.

You should learn just so you know what it can do for you. I wrote an enormous back end infrastructure for my last company to automate data analysis with the expectation that a pivot table would be the front end. It totally changed the whole process and drastically reduced the time it took for everyone to find data. You don't have to do this part for it to be an extremely useful tool, but I also stored pivot table settings in the database, so you could pre-select views without any interaction with the pivot configs.

If you use pivot tables in this manner, you can connect it to a database for automatic updates. In the situation described above, data from instruments in the lab would automatically appear in the pivot table/chart for everyone within a minute or two. The old way was to send spreadsheets around to each person, which is archaic and, frankly, kind of stupid lol. By definition, as soon as you send someone a spreadsheet, it's outdated.

I know this isn't really related to the main topic, but I try to spread the word about pivot tables/charts whenever possible. Almost without exception, people adopt them after hearing about the potential time savings. It's amazing how many charts and views can be reduced into a single table/chart.

 

[rcpratt]

Anyone who thinks pivot tables are worthless obviously has no clue how to use them. They are unbelievably useful.

Meh. There are ways to accomplish the same thing much better, easier, and quicker if you know the proper formulas. But pivot tables are definitely useful for those that don't.

 

[Tweak155]

You should learn just so you know what it can do for you. I wrote an enormous back end infrastructure for my last company to automate data analysis with the expectation that a pivot table would be the front end. It totally changed the whole process and drastically reduced the time it took for everyone to find data. You don't have to do this part for it to be an extremely useful tool, but I also stored pivot table settings in the database, so you could pre-select views without any interaction with the pivot configs.

If you use pivot tables in this manner, you can connect it to a database for automatic updates. In the situation described above, data from instruments in the lab would automatically appear in the pivot table/chart for everyone within a minute or two. The old way was to send spreadsheets around to each person, which is archaic and, frankly, kind of stupid lol. By definition, as soon as you send someone a spreadsheet, it's outdated.

I know this isn't really related to the main topic, but I try to spread the word about pivot tables/charts whenever possible. Almost without exception, people adopt them after hearing about the potential time savings. It's amazing how many charts and views can be reduced into a single table/chart.

Yeah for what I do, they aren't really needed. If I needed them, I'm confident I can figure them out in minutes. The current project I work on has 10's of thousands of lines of VBA code... it's really a project not meant for excel.

 

[MrDudeMan]

Meh. There are ways to accomplish the same thing much better, easier, and quicker if you know the proper formulas. But pivot tables are definitely useful for those that don't.

This really doesn't make any sense. There are things a pivot table can do that no formula could ever replicate. Also, I'd love to see how you can visualize data, especially in a huge data set, using a formula (a la pivot chart). If you're going to make the bold claim that you can do something MUCH better, easier, and quicker than a pivot table, I'd love to hear some details. I'm open to new ideas, but this sounds like ignorance.

Pivot tables/charts aren't going to magically solve all of your problems, but you've undervalued them significantly if you think formulas are a total replacement.

 

[AreaCode707]

This really doesn't make any sense. There are things a pivot table can do that no formula could ever replicate. Also, I'd love to see how you can visualize data, especially in a huge data set, using a formula (a la pivot chart). If you're going to make the bold claim that you can do something MUCH better, easier, and quicker than a pivot table, I'd love to hear some details. I'm open to new ideas, but this sounds like ignorance.

Pivot tables/charts aren't going to magically solve all of your problems, but you've undervalued them significantly if you think formulas are a total replacement.

I actually did figure out how to replicate a pivot table with formulae, once upon a time. I forget how to do it now but it involved a magic key combo to get the formula to apply to a range rather than a cell, or something like that... it took FOREVER to figure out and the only reason to do it that way was to avoid having to refresh the pivot table data.

Pivot tables and vlookups are the two most valuable things I ever learned to do in Excel. Cannot recommend them highly enough as "must-learns" for anyone working in Excel for more than two hours at a time.

 

[MrDudeMan]

I actually did figure out how to replicate a pivot table with formulae, once upon a time. I forget how to do it now but it involved a magic key combo to get the formula to apply to a range rather than a cell, or something like that... it took FOREVER to figure out and the only reason to do it that way was to avoid having to refresh the pivot table data.

Pivot tables and vlookups are the two most valuable things I ever learned to do in Excel. Cannot recommend them highly enough as "must-learns" for anyone working in Excel for more than two hours at a time.

You're talking about an array formula, which is certainly a helpful concept, but it definitely doesn't invalidate a pivot table. Many of the more powerful uses of pivot tables/charts aren't possible with array formulas.

 

[RelaxTheMind]

i use macros for just about everything. hotkeyed to mousebutton + letter combos. i can sort, reshape, highlight. etc just about anything i want on one excel sheet.

if you arent capable of doing the code for macros which you can pretty much copy and paste from people who already made them you can even use a record macro function which will basically just record your sequence of inputs.

 

[MrDudeMan]

i use macros for just about everything. hotkeyed to mousebutton + letter combos. i can sort, reshape, highlight. etc just about anything i want on one excel sheet.

if you arent capable of doing the code for macros which you can pretty much copy and paste from people who already made them you can even use a record macro function which will basically just record your sequence of inputs.

That's fine for some types of data. It can be far less efficient to write macros and UDFs for dynamic data sets compared to simply using built-in tools depending on the situation.

 

[TridenT]

References weren't good enough from what I tried. It don't copy formatting. (highlighting and so forth)

Seriously, I just want an exact copy of the other sheet but sorted differently and updated dynamically.

 

[MrDudeMan]

References weren't good enough from what I tried. It don't copy formatting. (highlighting and so forth)

Seriously, I just want an exact copy of the other sheet but sorted differently and updated dynamically.

Send me the spreadsheet and I'll do it for you.