VLOOKUP or INDEX to show all items added last month that have not yet been paid for

Hey, I'm guessing that all you want is formulas on the worksheet labeled "juntar notas". If so, then remove the data validation list from range 'juntar nota'!A4:A20. Enter this formula in A4 as an array [confirm with Ctrl+Shift+Enter at the same time]

Formula:

Please Login or Register  to view this content.

Drag down to A20, and you can protect the cells so that the formula is not tampered with or deleted. OHH, and ensure that you use the same custom text format you have in Dados!$B$2 so that it shows exactly how it appears in the Dados worksheet.

THEN, in C4:C20 enter [confirm with just Enter]:

Formula:

Please Login or Register  to view this content.

I suggest you change the formula in C2 to this:

Formula:

Please Login or Register  to view this content.

So that it always equal to the same day of the previous month.

Originally Posted by danwoltrs

Thanks Ron, it partially worked. It will show all the data from past month, but also from this current month.

Also, if there was something already paid, I put on the sheet 'Dados' as pago (paid), this should also not show up - is there a way to do this double verification?






I did as per your suggestion, much better

The formula is based on the dates on A2 and C2. It is showing you current month because A2 is February 7. If you don't want current month then change the date on A2 to be January 31. Would you still like to keep A2 with the formula =Today(), in this case February 7 and have the results only show unpaid invoices from January 1 thru January 31st, that is possible. Right now it will show everything from January 7 thru February 7.

What will indicate that an invoice has been paid in the worksheet "Dados"? The word "Pago" on Column G?

All of the above can be easily accommodated with a slight change to the formula.

Originally Posted by ron2k_1

The formula is based on the dates on A2 and C2. It is showing you current month because A2 is February 7. If you don't want current month then change the date on A2 to be January 31. Would you still like to keep A2 with the formula =Today(), in this case February 7 and have the results only show unpaid invoices from January 1 thru January 31st, that is possible. Right now it will show everything from January 7 thru February 7.

understood, but since I also need a today's date because this will be printed out, I made a "invisible" (white text) date with first day of the month on cell D2 [ =EOMONTH(A2,-1)+1 ] and changed on the formula, it worked!

Originally Posted by ron2k_1

What will indicate that an invoice has been paid in the worksheet "Dados"? The word "Pago" on Column G?

Yes, any word different than "" empty could work, but I guess I'll just leave a validity dropdown with 'Pago'

Originally Posted by danwoltrs

understood, but since I also need a today's date because this will be printed out, I made a "invisible" (white text) date with first day of the month on cell D2 [ =EOMONTH(A2,-1)+1 ] and changed on the formula, it worked!




Yes, any word different than "" empty could work, but I guess I'll just leave a validity dropdown with 'Pago'

Sorry, I got busy yesterday and forgot to get back to you. So, just to be sure. If today's date is February 9th, which invoice dates would you like to see on the list? You don't need a formula in D2, I can place the validation date in the formula if want it hardcoded. The options are:
January 1-31st
January 1-February 9th
January 9-31st
January 9 - February 9 or anything in between

Secondly, I'll assume you'll have a validation list dropdown option called "Pago" on the Dados worksheet column G.

PS: Your English is excellent by way. I see you're from Brazil.

I had a chance during my lunch break and worked on the attachment. Let me know if that is what you were hoping for. Right now it will filter invoices dated in the previous month to the date in A2 from the 1st of the month through to the last day of that month. So, in today's case, A2=February 9, 2018; the sheet will filter out all invoices not marked "Pago" in Dados from January 1st to January 31, 2018 (both dates inclusive).

If you want invoices in the previous month not to start from the 1st but from the 9th (today's day), then change this piece on the formula and remember to confirm with Ctrl Shift Enter and drag down:
Change this:

Formula:

Please Login or Register  to view this content.

To this:

Formula:

Please Login or Register  to view this content.

If you notice, the formula does not use the date in C2 [or D2], so you can delete it. It is only using the date in A2.

Still let me know if you require anything else. If not, then don't forget to mark your question SOLVED.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.