Grouping Sheets: Would This Be a Useful Feature?

[rjbinney]

*I* think this could be useful - but wondering what others think...

I have a bunch of tabs for monthly expenses. It's helpful to keep them in one file (instead of a separate file for each year, say), but it's unwieldy sometimes.

Wouldn't it be cool (I say) to right click on a whole bunch of them and put them in a "Master Tab" - which only expands if I double-click on it? Kind of like a Zip file or a folder within my document?

Wouldn't that be cool?

Wouldn't it?

 

[Joe4]

Sounds like what you really have is a database, in which case it would work much better in a database program, like Microsoft Access, than Excel (trying to perform database functions in Excel can be cumbersome and inefficient).

You don't need separate tabs for each month in Access. You have all your expenses data in one table, and can use dynamic queries to indicate what period you want to return the data for (one query to rule them all!).

Note that Excel does now have Power Query, which incorporates database like techniques (especially queries) in Excel. There is a "Power Tools" forum on this board to deal with question specific to Power Query and the other Power products.

 

[rjbinney]

Every three years or so I find something where the correct answer is "That belongs in Access." So I reteach myself Access and never touch it again. So when I need the data, I end up rebuilding spreadsheets. Or just making something up.

It would have been much nicer to just say, "Wow, yeah. That sounds really cool."

:|

ETA: "Light a man a fire, keep him warm for the rest of the night. Light a man afire, keep him warm for the rest of his life!"

 

[Joe4]

Every three years or so I find something where the correct answer is "That belongs in Access." So I reteach myself Access and never touch it again. So when I need the data, I end up rebuilding spreadsheets. Or just making something up.

It would have been much nicer to just say, "Wow, yeah. That sounds really cool."

:|

ETA: "Light a man a fire, keep him warm for the rest of the night. Light a man afire, keep him warm for the rest of his life!"

It sounds like an ongoing thing (monthly expenses), so why wouldn't you continue to use it?

This kind of situation reminds me of a quote an old MVP (who had a clever way with words) used to say (and I am paraphrasing).
"You could use a wrench to drive nails, but why would you?"

I was once where you are right now, but I was "forced" (not given an option) to create a database in Access for work. It was painful at first, as I tried to wing my way through it, and discovered that the learning curve for Access is a big steeper than it is for Excel. So I bit the bullet, got a few books (intro to Access, into to Relational Database theory, and an Access VBA book), and worked through them.

It was a bit painful at first, but I got some good help at forums like this one, and ended up creating a really slick database that worked SO MUCH better than anything I could whip up in Excel. I created a few more databases, and now really don't have a preference of one over the other (Excel vs. Access), I use the one that makes the most sense. I hardly ever use Excel forms anymore, because Access forms are so much easier (it is easy to "bind" the fields on the form to the data tables directly, so you don't need to write VBA code to assign the values from the form to your spreadsheet like you have to with Excel). And I find matching and filtering data so much easier in Access than in Excel.

I understand the hesitancy of "making the jump", but if you do it right, I don't think you will regret it. And you will have a cool new tool in your toolbelt.

Side note: I used to play a lot of Fantasy Football, and created an Access database where you enter in all the league rules, and player statistics, and it would spit out projected points for each player based on their average stats. I re-used it for years, and it really helped me with the drafts, ranking players, and understanding where the big drop-offs occur. Helped me win more than my share of championships! I guess my point here is once you get used to using it, you can have some fun with it.

 

[rjbinney]

Well, this file, sure, I use it a lot. But it's also a mandate to use Excel so...

I tried to put all of my records in Access, and then cross-reference them to different "Best Of" lists (Rolling Stone, WXPN, etc.). In Excel, it's a little clunky, but easy to accommodate multiple copies (e.g., an LP, CD, and 30th-Anniversary Box Set), but less so in Access, for reasons too tired to go into.

But where it REALLY broke down was in alphabetizing. I have a pretty good one-to-many system for artists. The Who goes under "W" and the Beatles go under "B", of course (don't get me started on where Black Sabbath and Led Zeppelin go, though!)... And Bruce Springsteen goes under "S", as does Bruce Springsteen and the E Street Band. But Dave Grohl goes under "F" for Foo Fighters. So does "The Dee Gees". OK, that can be coded into lookup tables. BUT... What about "Hans Zimmer"?!? The No Time to Die LP gets filed with "Bond Soundtracks" and the Dark Knight Rises CD gets filed with "Batman Soundtracks"... And so on...

 

[Joe4]

Most issues people have with Access is data table design issues, specifically, following the Rules on Normalization (that is where the book on Relational Database Theory really came in handy, but you can glean most of the important details on that from some tutorials you can find on-line).
If you create data tables in Access that are not normalized, you will have a horrible time, and even seemingly simple tasks become cumbersome and much harder than they should be.
However, if you create the data tables following the Rules of Normalization, you can do almost anything you can think of.

This was the hardest lessen for me to learn. My tables were DEFINITELY not normalized, and I was pulling my hair out in frustration.
When I took the time to learn these rules and apply them, everything fell nicely into place, and opened up a world of possibility.
In my opinion, that is probably the most important thing in database design, making sure your data tables are all normalized.