I need some help with a formula please

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
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.
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi Nicky,

It would be easier if you shared a sample sheet. Nonetheless, have you tried to change "Matrix" to the specific range without selecting the headings row?

Best Regards
M. Yusuf
 
Upvote 0
Hi Nicky,

It would be easier if you shared a sample sheet. Nonetheless, have you tried to change "Matrix" to the specific range without selecting the headings row?

Best Regards
M. Yusuf
Noted on the sample sheet for next time, but thank-you - that was just the perfect hint! Resolved :)
Nicky
 
Upvote 0
Most welcome Nicky, I am glad it is resolved ?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top