Forum Discussion
Macro to change sheet displayed
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.
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?
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 )- That's actually the best outcome: for you to figure it out yourself! Well done.
When you have time, I encourage you to delve in to the LET function. You could use it to make your formula more efficient (and shorter). But no rush......keep learning! - mathetes
Ah, while preparing it to send I figured out the error. If you were interested I used the if_empty part in order to achieve this, so the formula now looks like this.
=SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Month]=Presentation!F3)*(Table1[Site]=Presentation!A3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Site]=Presentation!A3)),2,-1)),2,-1)
Again, thank you for your help. I was running through everything I learnt from the video and the answer struck me. 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.
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
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. 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
