Adding additional 'IF' statements into a dynamic list formula

[dancing-shadow]

I have a spreadsheet of invoices - main information I'm using on it is the date the order was placed (col A - named as Sales_Dates), invoice number (col D - Sales_Inv), the date the invoice was paid (col H - Sales_DatesPaid)

I'm trying to create a formula that pulls out a list of invoices that have payments outstanding for the end of the previous month.

So assuming the month end date is 31/08/12 - If the order was placed before this date, and the date paid is either blank (ie, unpaid), or date paid falls after this date, then the invoice number is pulled through. But I would really like this as a dynamic list, rather than having a full list and having to filter out the blanks.


I think I need the array formula

Please Login or Register  to view this content.

to incorporate some additional 'IF' statements

Please Login or Register  to view this content.

-

Where Q1 = the date of the previous month end (last Friday of the month)

Please Login or Register  to view this content.

Can someone help create the final formula for what I need?

[Fotis1991]

Perhaps if we have a small sampe workbook?

[dancing-shadow]

Ok, see how this works for you...

The formula I'm wanting to create needs to go in the 2nd tab, yellow column

For the outstanding list for August, I need it to pull through the following rows:

5, 7, 8, 13, 23, 24, 29, 35, 36 - as these accounts/invoices are overdue.

Also rows 15 and 17, as these were paid after the month end date (21/08/12)

Row 41 is not needed, as although it hasn't been paid yet, the Date it was ordered was after the month end date.


Regarding the date used - please see my explanation above of where I get the month end date from.

[Fotis1991]

Hi

One way to do this.

In Q2(We use this as helper and hidden-if you like-column) and copy down, of Sheet 2, put this formula.

Please Login or Register  to view this content.

This find the unpaid and the invoices that their payment was after the last day of the previos month and mark them.

Then in sheet3, in A2 put this Array Formula(C+Sh+E) and copy down and across as mutch as you need.

Please Login or Register  to view this content.

In this sheet i also use Conditional Formatting, to highlight the payments that are overdue.

[dancing-shadow]

Wow..!! That's even better than I expected it to be :D Thank you for your time and effort that's gone into this!

Just one slight amendment though - on Sheet3 it also pulls through two lines which are not needed - lines 13 and 14 - as these orders were placed on the 3rd September (after the month end date of the 31st Aug), they do not need to be included in my outstanding list. Is there a tweek to disregard anything after the month end date?


Edit:
No matter - I've amended the helper column to not show 'UN' if the date ordered is after the month end date. This now works perfectly.

Many thanks again for your help Fotis - σας ευχαριστώ

[Fotis1991]

Hi

I am glad that you like this.

A slight modification in formula in helper column of shhet 2. Put this in q2 and copy down.

Please Login or Register  to view this content.

Is it OK, now?

[Fotis1991]

I just saw your Edit in post#5. So.

You are welcome!(Παρακαλώ!)

Thanks for the reb*