formula or vba auto number based on two values

[leap out]

good day
I would auto number like my picture based on two value if in column a = " paid" & "unpaid " then insert in column b ,the column b contain value is 1 expresses the current month and if it enter month Feb then change to 2

Microsoft Excel .xlsx
	A	B
1	UNPAID	INVOICE NUMBER INV-1-1000
2	UNPAID	INVOICE NUMBER INV-1-1001
3	PAID	VOUCHER NUMBER VOUCHER-1-C1000
4	PAID	VOUCHER NUMBER VOUCHER-1-C1001
ورقة1

 

[jasonb75]

Here is one method that works.

**** Before entering the formula you will need to go to Excel Options, then to the Formulas tab and check the 'Enable iterative calculation' box, if you do not do this then you will get a circular reference warning and the formula will fail. ****

Note that I have started in row 2, you can have headers in row 1 but not data.

Book1
	A	B
2	UNPAID	INVOICE NUMBER INV-1-1000
3	UNPAID	INVOICE NUMBER INV-1-1001
4	PAID	VOUCHER NUMBER VOUCHER-1-C1000
5	PAID	VOUCHER NUMBER VOUCHER-1-C1001
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2="","",IF(AND(B2<>"",B2<>0,ISNA(MATCH(B2,B$1:B1,0))),B2,IF(A2="UNPAID","INVOICE NUMBER INV-"&MONTH(TODAY())&"-"&(COUNTIF(B$1:B1,"INVOICE NUMBER INV-"&MONTH(TODAY())&"-*")+1000),"VOUCHER NUMBER VOUCHER-"&MONTH(TODAY())&"-C"&(COUNTIF(B$1:B1,"VOUCHER NUMBER VOUCHER-"&MONTH(TODAY())&"-C*")+1000))))

 

[leap out]

hi Jason
great formula , but I have a problem this is what I got not like your result , do you have any idea ?

 

[jasonb75]

this is what I got not like your result

I can't see anything in your post showing what went wrong. Did you set the iterative calculation and move the data down to row 2 as per my suggestion?

 

[leap out]

I did it and works but not show this

UNPAID

INVOICE NUMBER INV-1-1000

it supposes when write unpaid shows INVOICE NUMBER INV-1-1000

ورقة عمل Microsoft Excel جديد.xlsx
	A	B
2	UNPAID	VOUCHER NUMBER VOUCHER-1-C1000
3	PAID	VOUCHER NUMBER VOUCHER-1-C1001
4	PAID	VOUCHER NUMBER VOUCHER-1-C1002
sh

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",IF(AND(B2<>"",B2<>0,ISNA(MATCH(B2,B$1:B1,0))),B2,IF(A2="UNPAID","INVOICE NUMBER INV-"&MONTH(TODAY())&"-"&(COUNTIF(B$1:B1,"INVOICE NUMBER INV-"&MONTH(TODAY())&"-*")+1000),"VOUCHER NUMBER VOUCHER-"&MONTH(TODAY())&"-C"&(COUNTIF(B$1:B1,"VOUCHER NUMBER VOUCHER-"&MONTH(TODAY())&"-C*")+1000))))

 

[leap out]

hi Jason

I tried with a new workbook then it successes I no know if it is because when show this message "get a circular reference warning and the formula " before I select from option , any way many thanks for your assistance

 

[jasonb75]

You have a space after UNPAID which is causing a mismatch, the formula looks for "UNPAID" not "UNPAID "

Select A2, then press the Delete key to clear the cell and retype UNPAID without a space.

Note that if you don't clear A2 first then the formula will not update. This is necessary to stop the dates from changing incorrectly.

 

[leap out]

thanks for your notice and correcting me
the formula is a great
I appreciate your assistance