Help creating an unpaid invoice dashboard

[Natalia Kataoka]

Hello all,

I keep a spreadsheet tracking our outgoing invoices & payments and want to create a dashboard to track those invoices that are UNpaid. In my spreadsheet I have individual tabs for each client that lists all invoices with their process date, payment date and days between these two dates (among other things).

In the dashboard, I would like each unpaid invoice to appear along with their invoice #, their original process date and the days between based on the current date. I understand how to formulate the 'days between' function as well as how to pull data from a different tab (for a single cell only), HOWEVER, what I cannot figure out how to do is have those unpaid invoices appear on the dashboard AUTOMATICALLY based upon whether the "paid" cell has content entered into it. Additionally, I would hope that once a 'paid' date has been entered that that entry would be removed from the dashboard.

Grouping by company/tab would be a plus, as well.

I have attached an example of what I would like to do, just can't figure out the 'automatic' portion.

TIA!

 

[eibi]

Natalia,

Welcome to the forum!

If you are willing/able to keep all your invoices in a single table rather than on separate tabs, I think you'll be satisfied that a Pivot Table will do all that you've asked of the dashboard...

Have you considered a Pivot Table for this application? (see attached)

 

[Natalia Kataoka]

Thanks, Eibi.

Unfortunately, due to company standards, I cannot put all the invoices on a single table. I do not have experience with Pivot tables so it never crossed my mind!

Is there a way to do this with the multiple tabs?

 

[eibi]

If the boss expects you to develop reports that require all the data to be crunched to create a single output, I'd really encourage you to lean on the boss to put all the data into a single input. You can reassure him or her that even when it's all in one table, you can simply AutoFilter by Client any time you want a Client-specific list of invoices. Or, just as easily, you can autofilter for unpaid invoices -- in the same table...Or for paid invoices...Or for any other criteria...No dashboard required.

...Or, if you really want separate tabs, you can create separate tabs that read from the common table.
-------------------------------------------------------------------

Meantime, since bosses don't always listen, see if the attached file is a helpful starting point to satisfy your original request...

In order to keep the data on separate tabs, you will have to set up a list of tab names somewhere (see cells J1:J4). Then, you need a master formula that cycles through those tab names and counts the number of unpaid invoices (Column A). Then, when you have the right rows set up in the first column, you reference that column to find the invoices and other details you need (Columns B, C,...).

Also, be warned: Because of having the data on separate tabs, this dashboard approach depends on lots of INDIRECT() formulas, which will eventually bog down your processing time if your dashboard grows too long.