Excel 2016 Conditional Formatting - Multiple Tabs

Copper Contributor

I have a spreadsheet that has over 100 tabs on it. The tab I want to format is our Balances tab, which shows which invoices have not yet been paid (high level view of what's outstanding). This Balances tab just shows the dollar amounts outstanding by client, not any dates or anything. The dollar amounts are based on the invoicing tab less the payment tab (two other tabs, so a formula) and those tabs are based on the actual fields from each individual tab from the workbook. I am hoping that within each of the 100+ tabs that we have per client, the Balances tab cells can change color for the dollar amounts that are past due (RED) or are upcoming in the next week (BLUE). Still with me? If so, the 100+ tabs have the details of each invoice, including columns with due date, amount due, payment received date, payment amount (to name a few). Does anyone know how to do conditional formatting in a summary tab, which has to look back across numerous sheets in one workbook?

1 Reply
You could use a formula similar to the one you currently have to get the balance to extract the due date from the information. Then it'll be easy to base a CF rule on that newly inserted column.
PS: As you have noticed, your multi-worksheet setup makes it hard to do cross-client reporting. Having the information of all invoices on a single tab would have made this a relatively straight-forward task.