Forum Discussion
Macro to change sheet displayed
Would it be possible to have one sheet that can be used to display any of the other sheets upon selection or command?
It would be a case of clearing its current contents and then pulling everything from another sheet, I think? Then the other sheets could be hidden.
Yes, that's possible. The INDIRECT function can do it. Depending on how those sheets are arrayed, FILTER could also work.
Sadly, without a better understanding of how your sheets are laid out (designed) it's hard to give more specific info. Is it possible, without disclosing confidential information, for you to post a copy to OneDrive or GoogleDrive and a link here granting access?
In general (again without seeing the existing design, hard to be certain) it probably is the case that the entire workbook could work FAR more efficiently if the separate monthly sheets were combined into a single database. It's a fairly common mistake for people unfamiliar with some of Excel's abilities to design workbooks as if they're still working with paper ledger sheets; in the latter (former) case, it made sense to keep months on distinct sheets; in Excel that practice actually gets in the way, making reporting--such as you're wanting to do now--more difficult.
Please find link to the file on Google drive below. I havent made any significant changes, this is how it is all set up and laid out. Certain fomulas are not yet complete but that won't impact anything.
https://drive.google.com/file/d/1NBWpLNdZbH4j63AOBOjXsSrIjyxQqdWM/view?usp=drivesdk
Oh, my. I strongly (STRONGLY!!) recommend that you combine all those monthly sheets into a single, ongoing database. Just add a column that contains the effective date of the data on that row. From such a re-designed database--unless I'm missing something--you'll be able to do all the analyses you want to do, certainly including year-over-year changes in each month. Are you open to such a change (or in a position to do so)?
- mathetes
That's what I WANT to do. As with other visualising tools it isn't necessary. However it needs to be something that can be easily viewed still hence the 2 sheets per month. Thats why I had the request to pull to and clear one singular sheet.
But yes the other way I had in mind as a last resort and see how it goes down is to combine it all and just filter by month as the key is to be comparing each month's history.