Hi there,
I've got this master table where I input grants that my organisation is applying for and I want to filter it into two groups.
Group 1. I want to filter data onto another sheet, where I can see which grants need to be submitted in the next 90 days.
Group 2. I also want to filter the data into another group on the same sheet as above, where I can see the grants we have submitted, and are pending a result. (I'm open to having these groups be on separate sheets if it helps).
Here's the raw data table (I've hidden some rows just to make this screenshot clearer)
I thought I had it working, but the formula was unwieldy.
For Group 1.
Regarding the upcoming grants (submission due between today and in 90 days time) I was using an IF(FILTER(INDEX.... formula to only show certain columns from the raw data, when the filter conditions were met (checking the Submission Due column in the raw data table).
The formula in the cell under Grant Name is
=IF(FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,13,3,4,8,14,26}),(Table1[Submissions Due]>=TODAY())*(Table1[Submissions Due]<=(TODAY()+90)),"")<>"",FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,13,3,4,8,14,26}),(Table1[Submissions Due]>=TODAY())*(Table1[Submissions Due]<=(TODAY()+90)),""),"")
And then I used =HYPERLINK(VLOOKUP(E6,Table1,5,FALSE)) in the cell to the left, to find a particular column. I did this because the IF(FILTER(INDEX... formula wasn't honouring the hyperlink that existed in the raw data table.
(And I used =HYPERLINK(VLOOKUP(E6,Table1,2,FALSE)) for the cell to the left of that one)
For 2.
Regarding the grants that have been submitted and are pending approval/rejection, I used a similar formula, and filtered rows of data that had something in the "Submitted On" column.
The formula under Grant Name looks like this
=IF(FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,13,9,11,12,15,26,27}),(Table1[Submissions Due]<=TODAY())*(Table1[Submitted on]<>"")*(Table1[Amount Granted]=""),"")<>"",FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,13,9,11,12,15,26,27}),(Table1[Submissions Due]<=TODAY())*(Table1[Submitted on]<>"")*(Table1[Amount Granted]=""),""),"")
And then for the three columns to the left, I used a VLOOKUP formula like above, so as to keep the hyperlinks working.
This is all so convoluted and I'm sure there's a better way. It's also currently broken.
The newest entry is Mary MacKillop 23. The grant is due Sept 22nd, and today is Sept 20th, so it is showing up in the To Be Submitted group. But I submitted the grant today, so I want it to appear in the Pending Result group.
By changing its "Submissions Due" date to the past in the Raw Data Table, the row does move into the Pending Result group. So I guess I could add another IF variable to the first formula, but it's already too much. Right?
THE OTHER THING is that I want to sort these groups chronologically. So for the To be Submitted group, I want the data sorted by "Submissions Due", and for the Pending Result group, I want the data sorted by "Result Expected", and I have no clue how to do this, despite trying many iterations of adding SORT to the formula.
Please help, this has been driving me mad for weeeeeks :s