Increasing invoice number.

Column N in my spreadsheet needs to generate an Invoice Number if certain criteria are met...

1. If column A has a "G" in it
2. If column D has a value in it
3. If column M > £0.00

However I want it to remain blank if these criteria are not met. If they are met then the number generated must be one higher than the previous number generated. The invoice numbers start at a number defined in cell B4.

Please help :-D

Hi,

In column N,

=IF(ISERROR(IF(FIND("G",A1)>0,IF(D1>0,IF(M1>0,B4=ROW(),""),""),"")),"",IF(FIND("G",A1)>0,IF(D1>0,IF(M1>0,B4+ROW(),""),""),""))

so long as you don't need the invoice numbers to be sequential, just incremenally larger.

HTH

Dave

Hi there, it doesn't seem to work... I have uploaded a .zip file if it helps (same as on other post)

But it should be something like this...

B4 = 1000

1st line ALL CRITERIA MET 1000
2nd Line ALL CRITERIA MET 1001
3rd Line CRITERIA NOT MET
4th Line ALL CRITERIA MET 1002

etc. etc.

Sorry - forgot my absolutes

=IF(ISERROR(IF(FIND("G",A8)>0,IF(D8>0,IF(M8>0,$B$4+ROW()-7,""),""),"")),"",IF(FIND("G",A8)>0,IF(D8>0,IF(M8>0,$B$4+ROW()-7,""),""),""))

but I can't get the invoice numbers to be incremental, without using VBA. Do you want to go that route??

Dave

No I think I have found a solution using another column, which if the criteria are met then it adds one to the provious number, if they aren't met it repeats the number.

The invoice number column then, if the criteria are met, puts in the value from the other column, and if they are not met, it leaves the cells blank.

Not very elegant though.

Thanks very much for your input though.