VBA for locking sheet after submission of report with date and time stamp

[jayanta1978]

Dear Friend,

Please help me with VBA or Functions for the following problem for my project.

1- in "Sheet" 1-31 is day wise report of an sales representative. , he will be submitting his call for the day and at the end of his submissions
he will click on "SUBMIT REPORT" button and his report will be for the day will be locked for editing, similarly it will be for Sheet 1-31
2- In each sheet "DATE OF SUBMISSION" i.e G4 cell will get the date and time stamp of his report submission.
3- In DR_LIST Sheet I need the Dr's visit dates of each Dr. reported (Preferably with function or formula)

PROJECT (Repaired).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	Daily Call Report For :			01/09/2021	Date of Submission:			Day :	Wed	Area As Per TP :		ASANSOL
5
6	SE NAME :		ABHIJIT BHANDARI			Worked With :	ZSM		Manager:		Sk.REJAUL BARI
7
8				Area Worked 1:		RUPNARAYAN PUR
9	Call	Mor Eve	Dr.Name	SPEC	MCL NO	P1	P2	P3	INPUT	CHEMIST	POB	STOCKISTS
10	1
11	2
12	3
13	4
14	5
15	6
16	7
17	8
18	9
19	10
20	11
21	12
22	13
23	14
24	15
25			0
26				Area Worked 2:
27	Call	Mor Eve	Dr.Name	SPEC	MCL NO	P1	P2	P3	INPUT	CHEMIST	POB	STOCKISTS
28	16
29	17
30	18
31	19
32	20
33	21
34	22
35	23
36	24
37	25
38			0
39				Area Worked 3:
40	Call	Mor Eve	Dr.Name	SPEC	MCL NO	P1	P2	P3	INPUT	CHEMIST	POB	STOCKISTS
41	16	EVE
42	17
43	18
44	19
45	20
46			0
47	END OF REPORT
60
61
1

Cell Formulas
Range	Formula
D4	D4	=TP!$B$7
I4	I4	=TP!$C$7
L4	L4	=TP!$D$7
C6	C6	=TP!$E$4
K6	K6	=IFERROR(VLOOKUP($G$6,'DATA CALCULATIONS'!$N$3:$O$5,2,0),"")
D41:D45,D28:D37,D10:D24	D10	=IFERROR(VLOOKUP($C10,DR_LIST!$I$2:$L$121,4,0),"")
E41:E45,E28:E37,E10:E24	E10	=IFERROR(INDEX(DR_LIST!$C$2:$C$121,MATCH('1'!$C10,DR_LIST!$I$2:$I$121,0)),"")
C25	C25	=COUNTA(C10:C24)
C38	C38	=COUNTA(C28:C37)
C46	C46	=COUNTA(C41:C45)

Cells with Data Validation
Cell	Allow	Criteria
F8	List	=OFFSET(MASTER!$D$2,1,MATCH($C$6,MASTER!$D$2:$S$2,0)-1,15,1)
F26	List	=OFFSET(MASTER!$D$2,1,MATCH($C$6,MASTER!$D$2:$S$2,0)-1,15,1)
F39	List	=OFFSET(MASTER!$D$2,1,MATCH($C$6,MASTER!$D$2:$S$2,0)-1,15,1)
G6	List	='DATA CALCULATIONS'!$N$3:$N$5
F10:H24	List	=MASTER!$U$3:$U$11
F41:H45	List	=MASTER!$U$3:$U$11
F28:H37	List	=MASTER!$U$3:$U$11
B10:B24	List	='DATA CALCULATIONS'!$R$3:$R$4
C10:C24	List	=OFFSET(MASTER!$E$2,1,MATCH($F$8,MASTER!$E$2:$S$2,0)-1,COUNTA(OFFSET(MASTER!$E$2,1,MATCH($F$8,MASTER!$E$2:$S$2,0)-1,90)),1)
B41:B45	List	='DATA CALCULATIONS'!$R$3:$R$4
C41:C45	List	=OFFSET(MASTER!$E$2,1,MATCH($F$39,MASTER!$E$2:$S$2,0)-1,COUNTA(OFFSET(MASTER!$E$2,1,MATCH($F$39,MASTER!$E$2:$S$2,0)-1,90)),1)
B28:B37	List	='DATA CALCULATIONS'!$R$3:$R$4
C28:C37	List	=OFFSET(MASTER!$E$2,1,MATCH($F$26,MASTER!$E$2:$S$2,0)-1,COUNTA(OFFSET(MASTER!$E$2,1,MATCH($F$26,MASTER!$E$2:$S$2,0)-1,90)),1)
J28:J37	List	='CHEMIST LIST'!$B$2:$B$101
J10:J24	List	='CHEMIST LIST'!$B$2:$B$101
J41:J45	List	='CHEMIST LIST'!$B$2:$B$101
L10:L24	List	='CHEMIST LIST'!G$2:G$7
L41:L45	List	='CHEMIST LIST'!G$2:G$7
L28:L37	List	='CHEMIST LIST'!G$2:G$7

PROJECT (Repaired).xlsx
	D	E	F	G	H	I	J	K	L										V	W	X
1	CODE NO	HQ	FIRST NAME	MIDDLE NAME	SURNAME	FULL NAME	GENDER	AREA	SPEC										VISIT DATES	VISIT DATES	VISIT DATES
2	5	ASANSOL	SUNIL		GUPTA	SUNIL GUPTA	MALE	ASANSOL	GENERAL PHYSICIAN
3	1	ASANSOL	SIDDHARTHA		BANNERJEE	SIDDHARTHA BANNERJEE	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
4	1	ASANSOL	JN.P.K		BANNERJEE	JN.P.K BANNERJEE	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
5	5	ASANSOL	N.K		Dutta	N.K Dutta	MALE	ASANSOL	GENERAL PHYSICIAN
6	8	ASANSOL	R.K		GUPTA	R.K GUPTA	MALE	ASANSOL	DERMATOLOGIST
7	1	ASANSOL	ATISH		SARKAR	ATISH SARKAR	MALE	RANIGUNJ	PHYSICIAN/CARDIOLOGIST
8	8	ASANSOL	SHYAMAL		SANYAL	SHYAMAL SANYAL	MALE	ASANSOL	DERMATOLOGIST
9	1	ASANSOL	SUBHADEEP		GHOSH	SUBHADEEP GHOSH	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
10	5	ASANSOL	A.K		PODDAR	A.K PODDAR	MALE	BARAKAR	GENERAL PHYSICIAN
11	7	ASANSOL	A.K		ROY	A.K ROY	MALE	ASANSOL	DENTIST
12	5	ASANSOL	A.K		SONTHALIYA	A.K SONTHALIYA	MALE	RANIGUNJ	GENERAL PHYSICIAN
13	5	ASANSOL	N.K		KALOTIA	N.K KALOTIA	MALE	RANIGUNJ	GENERAL PHYSICIAN
14	1	ASANSOL	J.N		MODOK	J.N MODOK	MALE	RUPNARAYAN PUR	PHYSICIAN/CARDIOLOGIST
15	1	ASANSOL	P		KARMAKAR	P KARMAKAR	MALE	RANIGUNJ	PHYSICIAN/CARDIOLOGIST
16	5	ASANSOL	P.S		BANNERJEE	P.S BANNERJEE	MALE	ASANSOL	GENERAL PHYSICIAN
17	1	ASANSOL	S		THAKUR	S THAKUR	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
18	1	ASANSOL	SUMANJIT		CHOWDHURY	SUMANJIT CHOWDHURY	MALE	KULTI	PHYSICIAN/CARDIOLOGIST
19	4	ASANSOL	J.B		HAZRA	J.B HAZRA	MALE	ASANSOL	ENT
20	1	ASANSOL	KALYAN		MONDAL	KALYAN MONDAL	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
21	1	ASANSOL	S.K		BANNERJEE	S.K BANNERJEE	MALE	ASANSOL	PHYSICIAN/CARDIOLOGIST
DR_LIST

Cell Formulas
Range	Formula
I2:I21	I2	=DR_LIST!$F2&" "&DR_LIST!$G2&" "&DR_LIST!$H2
L2:L21	L2	=VLOOKUP(DR_LIST!$D2,'DATA CALCULATIONS'!$C$3:$D$12,2,0)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Please help me with VBA code for Sheet lock and Date Stamp
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.