cafeteria_food
New Member
- Joined
- Jan 17, 2018
- Messages
- 3
Hello - I have been racking my head trying to figure this one out... Lots of google searches and searching MrExcel have left me still wondering.
I have a matrix in excel where the top header is general ledger accounts and the left hand column is locations. When the two are combined you have a general ledger string that searches our accounting database and returns the end of month account balances. For each GL account there is a dollar threshold which requires us physically review the balance and sign off on it. The matrix is set up with conditional formatting so if the balance in any cell is higher than the threshold it conditionally formats its red. If the account balance is less than the threshold nothing happens to the cell. I need a way to summarize all the accounts in the matrix with conditionally formatted red backgrounds into a list for review. The matrix is big (18,000 cells) so manually going through it looking for accounts and district numbers is not possible.
The link below is a screenshot of the matrix. As an example, the list would return the following info (district, account, and balance):
3063, 11500, $5,605,159.28
6090, 11500, $3,098,027.54
5025, 15501, $5,044,018.51
3067, 11501, $3,129,789.33
etc. etc. etc.
Matrix Screenshot Link:
https://imgur.com/a/vlHG8
I have a matrix in excel where the top header is general ledger accounts and the left hand column is locations. When the two are combined you have a general ledger string that searches our accounting database and returns the end of month account balances. For each GL account there is a dollar threshold which requires us physically review the balance and sign off on it. The matrix is set up with conditional formatting so if the balance in any cell is higher than the threshold it conditionally formats its red. If the account balance is less than the threshold nothing happens to the cell. I need a way to summarize all the accounts in the matrix with conditionally formatted red backgrounds into a list for review. The matrix is big (18,000 cells) so manually going through it looking for accounts and district numbers is not possible.
The link below is a screenshot of the matrix. As an example, the list would return the following info (district, account, and balance):
3063, 11500, $5,605,159.28
6090, 11500, $3,098,027.54
5025, 15501, $5,044,018.51
3067, 11501, $3,129,789.33
etc. etc. etc.
Matrix Screenshot Link:
https://imgur.com/a/vlHG8