Hi everyone,
I have created this formula:
=IF(F1="All Boroughs",(IFERROR(INDEX(SORT(Matrix,26,-1),SEQUENCE(E1+1),{4,5,6,7,26,32}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AB$400,MatrixScores!$E$1:$E$400=F1),26,-1),SEQUENCE(E1),{4,5,6,7,26,32}),"")))
Which does what I need it to do, but for one minor floor...
When I select a borough from a list displayed in cell 'F1' the results are brought back as expected under the respective 'headings' {4,5,6,7,26,32} - perfect!
However when I select "All Boroughs" it displays results as expected, but the first row repeats the headings, and the following rows provide a list of the top '10' boroughs from a scoring matrix. How can I stop this from happening? I just can't work it out!
Short background:
I have created a matrix to rate locations within boroughs against certain criteria, add those up to create a total score and then want to display the top n locations with the highest score (All Boroughs), or select a specific borough only and see the top n locations within that specified borough.
I'd be grateful for any advice you can give. I can, of course, provide more info if required, but I didn't want to put anyone to sleep with too much information if you don't need it, I'm guessing if you know what you're doing this is probably quite simple, even though I have spent several hours on it already - hence coming here for help!
Many thanks
Nicky
PS - I'm using Excel on 365 or the desktop version. But I'd really like this to work in 365 as it ideally needs to be a shared file.
I have created this formula:
=IF(F1="All Boroughs",(IFERROR(INDEX(SORT(Matrix,26,-1),SEQUENCE(E1+1),{4,5,6,7,26,32}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AB$400,MatrixScores!$E$1:$E$400=F1),26,-1),SEQUENCE(E1),{4,5,6,7,26,32}),"")))
Which does what I need it to do, but for one minor floor...
When I select a borough from a list displayed in cell 'F1' the results are brought back as expected under the respective 'headings' {4,5,6,7,26,32} - perfect!
However when I select "All Boroughs" it displays results as expected, but the first row repeats the headings, and the following rows provide a list of the top '10' boroughs from a scoring matrix. How can I stop this from happening? I just can't work it out!
Short background:
I have created a matrix to rate locations within boroughs against certain criteria, add those up to create a total score and then want to display the top n locations with the highest score (All Boroughs), or select a specific borough only and see the top n locations within that specified borough.
I'd be grateful for any advice you can give. I can, of course, provide more info if required, but I didn't want to put anyone to sleep with too much information if you don't need it, I'm guessing if you know what you're doing this is probably quite simple, even though I have spent several hours on it already - hence coming here for help!
Many thanks
Nicky
PS - I'm using Excel on 365 or the desktop version. But I'd really like this to work in 365 as it ideally needs to be a shared file.
Last edited:
