Forum Discussion
Reversing columns in a table
My engineer would like to not have to scroll over to see the newest data. Since this is ongoing the newest data keeps getting farther over, to where you can not see it without scrolling over. I have other books that is the same way that he would like to have changed.
One very simple solution to meet that desire, making an assumption on which data is most commonly viewed--seen on a regular basis--would be to simply hide the columns for years more than, oh, 5 years old. Keep the layout (and all the interrelationships and formulas) intact, and just hide the prior history. It would still be there if needed; summary pages would still have access....but scrolling would be eliminated. I've done an example of that on the 70X tab in the attached, just to demonstrate what's possible. [Note: it does become necessary to "unmerge" some of the header rows before you can do this, but that's a relatively simple matter.]
Another possible solution -- requiring a bit more work (to define it, first, and then to implement it)--would be to create a new "dashboard" sheet that extracts the data that the engineer needs to see from those subordinate sheets, pulling it together in one convenient spot. That would, as noted, require sitting down and thinking about what the needed output is.
My underlying point: it would be a mistake to totally reorganize what has been a smoothly functioning workbook for the sake of what is, admittedly, yes, more convenient, IF a few minutes devoted to answering the question "What do we really need to determine (or see or compare or....) in the first place?" could lead to a simpler solution that is also convenient (maybe even more so!).
I attached the file that I am working in. This is our department budget amount for each year. There is all kinds of links in this workbook. We want to change it so the most current year is in column B and have it go down in years after that. This is a copy of our file. I thought by changing the start here! page it would change the rest of the sheets but that did not happen. From going forward we would like to be able to insert a new column for each year in column B. I am not sure if I answered enough for you. Please bear with me. Thank you for taking the time to try and help, I appreciate it.
After looking over the workbook, and its many sheets, my first reaction is to ask "WHY are you wanting to change it?"
If it has been working, with all of its subordinate sheets organized with the earliest year on the left, moving to the right as years move on, this could well be a case of "If it ain't broke, don't fix it." And to the extent that you have graphs (a few) that show trends over time, going in the same direction (which happens to make sense to our "western way of thinking"--well, again, why change?
Based on your original question, I think both Riny_van_Eekelen and I (although I can't really speak for him) were picturing a single data table; that is most definitely not what you have here. Instead it's year-by-year actual and budget numbers for different departments or charge centers, each on their own sheets, with a final spreadsheet that compiles data from each of those charge centers.
Frankly, it makes sense to keep it going as it is. Unless there's some compelling reason--which is not evident from just looking at it--I'd think you'd want to keep it going as it's been going. Your request for help uses words like "we want" and "we'd like" when you refer to the change of order in the various columns; what you don't describe, and it's (in my mind) a major omission, is the reason underlying that "want" or that "like."
It may be that whatever output is produced by these sheets--that output itself could be changed WITHOUT monkeying with the underlying data. But I (for one) would want to know what purpose is being served by this proposed change.
- My engineer would like to not have to scroll over to see the newest data. Since this is ongoing the newest data keeps getting farther over, to where you can not see it without scrolling over. I have other books that is the same way that he would like to have changed. I am struggling with this.
My engineer would like to not have to scroll over to see the newest data. Since this is ongoing the newest data keeps getting farther over, to where you can not see it without scrolling over. I have other books that is the same way that he would like to have changed.
One very simple solution to meet that desire, making an assumption on which data is most commonly viewed--seen on a regular basis--would be to simply hide the columns for years more than, oh, 5 years old. Keep the layout (and all the interrelationships and formulas) intact, and just hide the prior history. It would still be there if needed; summary pages would still have access....but scrolling would be eliminated. I've done an example of that on the 70X tab in the attached, just to demonstrate what's possible. [Note: it does become necessary to "unmerge" some of the header rows before you can do this, but that's a relatively simple matter.]
Another possible solution -- requiring a bit more work (to define it, first, and then to implement it)--would be to create a new "dashboard" sheet that extracts the data that the engineer needs to see from those subordinate sheets, pulling it together in one convenient spot. That would, as noted, require sitting down and thinking about what the needed output is.
My underlying point: it would be a mistake to totally reorganize what has been a smoothly functioning workbook for the sake of what is, admittedly, yes, more convenient, IF a few minutes devoted to answering the question "What do we really need to determine (or see or compare or....) in the first place?" could lead to a simpler solution that is also convenient (maybe even more so!).
- Thank you for your help.