Thread: Excel Help again

I have a spreadsheet where I am tracking progress on a project, and I need to be able to automatically create an invoice if one has not already been done for a specific phase. I have created a similar sheet in Google Docs, so I could show you what I mean. You can view the spreadsheet here, https://docs.google.com/spreadsheet/...jlaNzVhNW5MNXc.

So I have another tab in the workbook that is my invoice. I would like it to fill only with the items that have not already been invoiced from each of the three phases. If the project does not have a completion date, or if the project already has an invoice date, then ignore that project.

Thanks in advance for the help, and let me know if I need to provide more information.

Use Datedif and if

I guess my question is more along the lines of how do I make row 1 on the invoice be the first item that hasn't already been invoiced, then row 2 the second, and so on, and have it skip items that either have not been completed yet, or have already been invoiced.

A filter, then sort by date?

Yeah, thought about that, but even if I sort then filter, or vice versa, the row #s still stay the same, it just hides the ones that don't meet the filter criteria. So for example, if I filtered out the ones that were already invoiced and the first one not invoiced was on row 27 it would still be on row 27, not row 2 now.

The only thing I can think of is to do a really long ass IF statement where if completion date 1 is greater than invoice date 1 then show the project 1 house number, else if completion date 2 is greater than invoice date 2... The problem with that is I will have about 175-200 houses with 5 projects per house. That formula would be disgusting.

You need a field that states whether or not an invoice has been created then you need if statements to correlate that.

Finally use your datedif statement to determine the set dates you want invoices to be created for..

also, by the looks of your demo spreadsheet you cant accomplish this unless each project is on a different sheet.

I can do the invoice dates just by saying =IF(completion date>invoice date, 0, 1) Then if I have a completion date and no invoice date I will get a 0, but if the invoice date is >= the completion date I will get a 1. Then I can sort by 0/1. Now the question is how do I tell it to add the lines, in order, for all of the zeros, and only the zeros?

Originally Posted by Warprosper

also, by the looks of your demo spreadsheet you cant accomplish this unless each project is on a different sheet.

Was thinking the same thing. Otherwise I am going to filter out lines I need for other projects. Easy enough.

Thought of using a database of some sort?