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?
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?
