Macro to change sheet displayed

Copper Contributor

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!

22 Replies

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

@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

@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

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

@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.
@mathetes

Ah, I recall now. Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??

@MattyNt 

Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??

 

Tell you what. Why don't you write out all of the current goals or objectives for this workbook. What are the outcomes, results, outputs (however you want to say it, whichever term is most appropriate)?

 

Working with a single, well-designed database is far and away the most solid foundation you can have for what would appear to be your situation. Excel has marvelous tools to parse data, enabling comparisons like

  • month-over-same-month-in-prior-year
  • month-over-prior-month
  • quarter-over-quarter
  • etc

The Pivot Table, for one, would enable you to do quite a bit of that. But also FILTER, functions like DGET (one of several data table extraction functions)

 

But let's begin with a full description of what you need in the way of output from all this input.

@mathetes 

 

The aim of this workbook is to compare the status and performances metrics of each centre on a month-over-same-month-in-prior-year basis, for example how full each centre is, how much income is generated and if businesses are from the target post code. Essentially it exists to display the data you currently see. I will ALSO be creating PowerBI visuals from this (hence I would much prefer a database) so it had to be suited for both, hence one table per sheet for PowerBI.

 

I have created formulas to display most recent data but they don't work on a filtering basis. My excel knowledge is limited as I do most visualising on Power BI so having them adapt to filtering goes a little further than what I have learnt.

Each centre has its own workbook but I want to change this to make it easier to access as well as edit, this can easily be done with a hidden column. I have been told this isn't required but demoing it working will go down very well so I have no concerns with that as the team are definitely looking to develop the data side

 

I completely agree with having the database. In previous jobs working on projects I've always worked with a database to then visualise from there, coming into this there is no database that spreadsheets are then created from which has made things unnecessarily convulated.

So: the next step is to combine all those separate monthly sheets into a single comprehensive database. (Retain the current in a copy so it can be used while you work on the "New & Improved" version. Add a column to the combined data that shows the effective month of the data on the row,
@mathetes

I have completed this, here is the link to this new file https://drive.google.com/file/d/1NicDnp9LTl-y1QVCCf4-iBLUqT8AaYQr/view?usp=drivesdk

I feel I should add regarding the purpose. This file is not for me to use during meetings, but someone else. I will not even be present for those meetings hence I want to make it as clean and easy to digest as I can.

@MattyNt 

 

Here is an Excel sheet -- saved as XLSX form because the way I've done this requires no macros.

 

This is just as a demonstration at this point, since I don't really know first hand what you need or want to be able to do. I've used the FILTER function to extract data from your database. (I also took the liberty of removing the percentage calculations from your database, since they're easily done "on the fly" as needed, rather than taking up space in the database. The FILTER function does require Excel 2021 or newer.

 

On the "Presentation" tab, you can change the entries in the yellow background cells to see how the reported information can be generated on the fly in response to questions. 

 

Note -- on the extraction by year, the months are sorted by alphabetical order. We can deal with that in coming days, depending more on what all you need in the way of reporting.

 

There is much more that could be done...you'll need to help articulate what is expected.

@mathetes 

 

I have been getting to grips with this today and it is fantastic.

 

Only initial changes I'm going to make are adjusting columns displayed on the presentation tab as well as an array of requires percentages I've managed to gather today.

I also need to display increases/decreases which I think I may look to colour code for further ease of use, but I add this to the right hand table fairly easily as far as I can tell.

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.

 

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.

 

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.

@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,

Apologies for the very late reply, I've been meaning to come back but haven't had the time this month. Essentially I want to add an extra column to the database for the 'Site', of which there are 3. This will create 3 sets of data for each month. I wondered if the tables on 'presentation' could be filtered by site as well as year?
@mathetes
Just an extra bit. On the left table, is it possible to sort it by month? Or some kind of date sorting other than alphabetical?

@MattyNt 

 

Essentially I want to add an extra column to the database for the 'Site', of which there are 3. This will create 3 sets of data for each month. I wondered if the tables on 'presentation' could be filtered by site as well as year?

 

Do NOT add an extra column for site; there is a column for site already; just add the data rows for each site and name the site that corresponds to the data in each row, just as you have it now for site LPC.

 

Yes, FILTER can handle both year and site as criteria.

 

Just an extra bit. On the left table, is it possible to sort it by month? Or some kind of date sorting other than alphabetical?

 

This could be done easily if you entered your dates as Excel date fields. They are sorted that way now because they're entered as names, and names get sorted alphabetically. So if instead of two columns, one for year and another for month, you were to use a "custom date format" that would display something like Nov-22 in a single column, and then sort it automatically in month order for the selected year.

mathetes_0-1669478937360.png

 

I gave you a link in an earlier posting to do your own research on FILTER and how it works. I strongly recommend you watch this video; it may enable you to accomplish what you desire on your own. But feel free to return if you still have questions.

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
@mathetes

Thank you for this. I got it all working but now have one final feature to make work which I think I've worked out but can't seem to make work;

When the cell for filtering by month is empty, it needs to show all months. I thought I could achieve this with the formula below but it comes back as #VALUE! . Through trial and error it seems like it needs the month filter, which it shouldn't?

=IF(ISBLANK(F3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*Table1[Site]=Presentation!A3),2,-1),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Month]=Presentation!F3)*(Table1[Site]=Presentation!A3)),2,-1))

F3 is the cell for filtering by month.

I can attach the current version if necessary.

@MattyNt 

 

When the cell for filtering by month is empty, it needs to show all months. I thought I could achieve this with the formula below but it comes back as #VALUE! . Through trial and error it seems like it needs the month filter, which it shouldn't?

=IF(ISBLANK(F3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*Table1[Site]=Presentation!A3),2,-1),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Month]=Presentation!F3)*(Table1[Site]=Presentation!A3)),2,-1))

 

It's hard to say without seeing the actual sheet. But at the very least, if you want all months to show, then you need to remove the portion of the criteria that refers to F3, the portion I've highlighted in bold and underlining above (including the "*" at the end). Leaving that section in but leaving F3 blank means that it's looking for all rows that have blank in the month field--which is no rows at all, presumably.

 

Your formula then would read as follows:

=IF(ISBLANK(F3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*Table1[Site]=Presentation!A3),2,-1),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Site]=Presentation!A3)),2,-1))

 

Have you watched the video I linked to in an earlier message? That was where I first learned the FILTER function. It's worth watching several times, in my opinion.

@mathetes 

 

The issue though is I want it to show all months only when the cell is blank. When it is populated I want it to filter. Hence the IF ISBLANK functions being utilised. So the first array in the formula does not have a month filter, but that array doesn't seem to work without it. The second array you have highlighted is how it will function when populated.

 

I've watched the video through twice which has been very useful for understanding it more. But unless I'm missing something it doesn't answer this last query.

 

I will attach the sheet shortly.