Macro to change sheet displayed

MattyNt 

Out of interest, attached is the verbose programming style for your problem.  It allows for the month selection to be deleted, it sorts by month by matching the month from a list, it calculates the percentages and inserts a blank column within the split output tables.  In short, it totally ignores years of spreadsheet development practice and goes its own way!

= LET(
      sourceData,     Table1[[Year]:[Sq M Let]],
      yearCriterion,  Table1[Year]>=selectedYear,
      monthCriterion, (Table1[Month]=selectedMonth) + ISBLANK(selectedMonth),
      filteredData,   FILTER(sourceData, yearCriterion*monthCriterion),
      filteredMonth,  XMATCH(CHOOSECOLS(filteredData,2), months),
      filteredYear,   CHOOSECOLS(filteredData,1),
      sortedData,     SORTBY(filteredData, filteredMonth,1, filteredYear,1),
      units,          CHOOSECOLS(sortedData, 4),
      occupied,       CHOOSECOLS(sortedData, 5),
      sqFootage,      CHOOSECOLS(sortedData, 6),
      sqFtgLet,       CHOOSECOLS(sortedData, 7),
      pcOccupied,     occupied / units,
      pcLet,          sqFtgLet / sqFootage,
      blank,          EXPAND({""}, ROWS(filteredData),,""),
      output,         HSTACK(sortedData, blank, pcOccupied, pcLet),
      output
   )

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.

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.

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.

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

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 

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 

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.