Showing a list of Accounts that are "RED" or "YELLOW" - Are you up to the challenge on this one!

[PCRIDE]

Hi, I have a operations dashboard showing a status of each service within an account whether the status is RED, YELLOW or GREEN based on % that I have configured. So each account may have 25 services where a RED, YELLOW or GREEN status will show in that area. I have counted the number of Greens using CountIFs to give me a % of Green status for all that service across all accounts. Everything is fine there.

Now I need to display the Account names that are Red or Yellow.

Here is my layout - In each Service there could be up to 50 Accounts, each having a Status. Then I simple create a percent "Green"

Service | Status
Production | Green
Assembly | Yellow
Packaging | RED
Shipping | GREEN
Design | GREEN
Mechanical | GREEN


So in order to display the Accounts that make up the RED and YELLOW status, which could be multiple accounts, I need to display them next to the Service, in the same row. Or if anyone has better ideas feel free. The idea is to know which Accounts are making up the RED and YELLOW so those accounts can be looked at in detail as to why.

There could be 20 accounts RED, or YELLOW so I need to figure out a good way to display the Account Names.

I can't do a pivot table because I would need too many pivot tables for each Service and I don't have room. Excel seems to corrupt when you have too many pivot tables and charts sometimes
I was thinking a =Concatenate formula based on a CountIFs (Count the number of RED accounts, then Concatenate them) Account A, Account B, Account C etc... - Not sure if this can be done

The data in the spread sheet is refreshed using a data connection to Sharepoint so I have only 1 data table feeding the sheet. I can make helper fields and other tabs if needed.

Got any ideas?

 

[PCRIDE]

Great NEWS! I was able to fix it. Turns out that my formulas had no limit on the number of rows I was looking up.


Service Dashboard'!$E$3,Data!$B:$B,Data!$AI:$AI

So I changed to $B2:$B127 and so forth!

now it works like a charm. Instant too!

I normally refer to the entire column but I guess on the bigger sheets its best to put a limit on the look up. I have never had this problem before, so It didn't come to me until I started the process of elimination and started deleting the Connection String, other tabs and formulas, all cells but one testing the code each time and it turns out even with 1 cell populated that doesn't have a limit on the lookup will cause about 3 second delay. So I guess its the custom function and applying it to the entire column length.


Rick thanks again!! and Mr Excel for such a great forum and user community.