add number based on date

[Trevor3007]

hi,

i know what i want it to do, but dont know if its viable

I have to invoice the customer each week . So for example :-

WC 11//2/19 has invoice number 9

is it possible to automate the invoice number (located in cell g1) to change accordingly based on the WC date (located in cell b4)?

so WC :-

18/2/19 invoice number = 10
25/2/19 ---||----- = 11
04/3/19 ---||----- = 12

ETC

i hoping some can sort.

Kindest regards & MTIA
Trevor3997

 

[vcoolio]

Hello Trevor,

I don't know what WC is supposed to represent but you could try the following Worksheet_Change event code:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If IsDate(Target) Then
Range("G1").Value = Range("G1").Value + 1
End If

End Sub

To implement this code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Fluff]

How about

Excel 2013/2016
	B	C
4	11/02/2019	9
5	18/02/2019	10
6	25/02/2019	11
7	04/03/2019	12
Prices

Cell Formulas
Range	Formula
C4	=WEEKNUM(B4)+2

 

[Trevor3007]

Hi,

Thank you for your help. I tried but it did not work... Fluff also said try/use =WEEKNUM(B4)+2 and it did.

I do appreciate your help & hope you have a good weekend.

KR
Trevor3007

 

[Trevor3007]

Hello Trevor,

I don't know what WC is supposed to represent but you could try the following Worksheet_Change event code:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If IsDate(Target) Then
Range("G1").Value = Range("G1").Value + 1
End If

End Sub

To implement this code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

WC = week commencing :}

 

[Fluff]

You're welcome & thanks for the feedback