Forum Discussion
Macro to change sheet displayed
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.
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.
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.
