How to create list based on retrieved values that meet a condition

[stevemalekano]

Hello,

I have a very simple query. I want to create three lists ("red", "amber", "green") of values based on data in another sheet. In the source sheet different districts (rows) receive a rating of 1, 2 or 3. In a separate sheet I want to create three lists that draw through a list of districts into three columns (1 = green, 2 = amber, 3 = red). I hope this makes sense! What formula can I use to retrieve the data into a table that would continue to update?

The screenshots below show an example of the source data and how the summary table could look. Many thanks in advance for your help.





Capture.PNG


Capture2.PNG

[kersplash]

Can you attach a sample workbook?

Go Advanced -> Manage Attachments -> Upload

[stevemalekano]

Thanks for the tip - please see attached.

[stevemalekano]

PS I am not wedded to what the summary table would look like - I just want it to pull through the districts into a table with 3 columns (green, amber, red) based on whether each district receives a 1, 2 or 3 score in the second sheet. Many thanks for your help!

[AliGW]

In B4:

=IFERROR(INDEX('wc 2805'!$B:$B,SMALL(IF(Table1[Summary assessment: Overall, how well is the marketing process currently functioning across the district? (1 = no/few issues, 2 = some issues, 3 = many issues)]=COLUMNS($A:A),ROW(Table1[District])),ROWS($A$1:$A1))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Once confirmed, copy and paste to C4 and D4, then select B4:D4 and drag copy down.

[stevemalekano]

Thank you so much! This worked perfectly.

[AliGW]

Yep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[stevemalekano]

Thank you - I had just a small follow-up query for the same table if that's possible.

I would like the districts that satisfy one other criteria to be highlighted e.g. in bold. This is a Y/N criteria in the second sheet for high priority or low priority. So, any districts that also were marked as high priority would appear in bold in the summary (green/amber/red) table. Is there a way to add that into the formula?

Thanks very much for your help!

[AliGW]

This is a completely different query (cannot be done using a formula) - mark this thread as solved and open a new thread for the new query, please.