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