Hi everyone,
I have achieved so much from everyone's help on here, but I need to impose once more if possible for what I hope to be reasonably easy with some of the experience clearly hanging out here! I am so grateful for all your help so far. I have two formulas that work fine independently:
Priority - which returns data from the same array, based on its priority score, either "All Priorities" or Priority 1/2/3/4
=IF(F1="All Priorities",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$I$1:$I$400=F1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))
Borough - which returns data from a larger table on a different tab to display it more easily for users based on either "all boroughs" or a selected borough from the array:
=IF(G1="All Boroughs",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$E$1:$E$400=G1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))
I have now been asked to be able to pull the data back based on Priority selection (selected from cell F1) and then by borough (selected from cell G1) in the same place - like a multi-choice I guess.
I thought It would be an 'AND' statement in the formula, but for the life of me, I can not make it work, but that maybe my inexperience - I haven't been writing formulas for very long and seem to have been pushed straight into the deep end! I love learning, but I am struggling.
I would be grateful for any support, suggestions or ideas you may have.
Many thanks
Nicky
PS - I am using Excel 365/Desktop 2016
I have achieved so much from everyone's help on here, but I need to impose once more if possible for what I hope to be reasonably easy with some of the experience clearly hanging out here! I am so grateful for all your help so far. I have two formulas that work fine independently:
Priority - which returns data from the same array, based on its priority score, either "All Priorities" or Priority 1/2/3/4
=IF(F1="All Priorities",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$I$1:$I$400=F1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))
Borough - which returns data from a larger table on a different tab to display it more easily for users based on either "all boroughs" or a selected borough from the array:
=IF(G1="All Boroughs",(IFERROR(INDEX(SORT(MatrixScores!$A$3:$AE$400,26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AE$400,MatrixScores!$E$1:$E$400=G1),26,-1),SEQUENCE(H1),{4,5,6,7,9,8,10}),"")))
I have now been asked to be able to pull the data back based on Priority selection (selected from cell F1) and then by borough (selected from cell G1) in the same place - like a multi-choice I guess.
I thought It would be an 'AND' statement in the formula, but for the life of me, I can not make it work, but that maybe my inexperience - I haven't been writing formulas for very long and seem to have been pushed straight into the deep end! I love learning, but I am struggling.
I would be grateful for any support, suggestions or ideas you may have.
Many thanks
Nicky
PS - I am using Excel 365/Desktop 2016