Hi all, I have been given a workbook that contains plenty of tabs that even my navigation macro feels a little pointless as they're similarly named. Each tab is a month showing yearly data, with a 2nd row I've added to compare the 2 most recent years. However it feels poor to navigate, as it will be used in meetings. I had a thought but I can't quite figure it out. 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. Is there a way of doing this? It's been a while since I've done excel. But I feel like this workbook can be a lot cleaner. My alternative is to take the time to combine all of the sheets and have a hidden column to filter by but I believe this would mess with my comparison functions... so changing displays would be preferable.Any help much appreciated!

MattyNt 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.

MattyNt One thing I wanted to ask was regarding the tables on the 'Presentation' tab. In order to add columns to these tables through the formulas, but not by increasing the range, how is this done? Do I just have multiple ranges in the formula? Haven't had the chance to test that yet. I'm not sure I understand what you're getting at with your questions. What columns are you wanting to add? The FILTER function, as you may have noticed, is what's called a Dynamic Array function, in that it is entered only into the top left cell...and all of the results "spill" from there, to the right and down. So adding columns would normally mean including more from the source database itself. I didn't include all of them, partly because I was just showing the possibilities, how FILTER could retrieve data specified by criteria, and do so from a single comprehensive database. If you don't mind me asking, what else did you have in mind for what else can be done? I'm always keen to learn and improve. I didn't have anything in particular in mind. It's more that I'd be happy to help you if there are specific types of reports that you are being expected to produce from this data, other types of analyses or summaries. Lastly I'd like to say thank you very much for your help so far, it's much appreciated and I've learnt a lot. You're most welcome. One of the reasons I enjoy answering inquiries like yours is that I learn in the process myself.

mathetes Thanks for the reply! Yes I'll make a copy of the file without anything confidential and post here asap.My preference is to have a database that can then be visualised in powerbi but it's a request for it to be like this in spreadsheets unfortunately so it's out of my hands.But I have permission try to clean it up as best as possible for consumption

mathetesPlease 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

MattyNt 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)?

Macro to change sheet displayed | Microsoft Community Hub

22 Replies

mathetes
Silver Contributor
Oct 31, 2022
MattyNt

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.
- MattyNt
  Copper Contributor
  Oct 31, 2022
  mathetes
  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
  - mathetes
    Silver Contributor
    Oct 31, 2022
    MattyNt
    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)?
- MattyNt
  Copper Contributor
  Oct 31, 2022
  mathetes
  Thanks for the reply! Yes I'll make a copy of the file without anything confidential and post here asap.
  My preference is to have a database that can then be visualised in powerbi but it's a request for it to be like this in spreadsheets unfortunately so it's out of my hands.
  But I have permission try to clean it up as best as possible for consumption

Forum Discussion

Macro to change sheet displayed

22 Replies

Resources