Copy paste value when selecting from drop-down list

[tinezi]

Hi,
I just can't figure out the VBA needed for this

When on 'Tenders' sheet and selecting for G15 "Ongoing", it should create an event that copypastes value from 'Data1' cell L6 to 'Tenders B15

When selecting "Ongoing" the code should recognize the company in question CompanyA/B/C/D in 'Tenders' and then copypaste from the value from Data1 L6/M6/N7/O6 to Tender-sheet.

In Data1 I have created a system that it generates the next available free project number for the companyA/B/C/D respectively. And always using the same cell where it will be generated.

tender-project-management.xlsm
	A	B	C	D	E	F	G
1	TENDERS
2
3
4	Tender number	Project number	Type	Group company	Project name	Price	STATUS
5	T603	CompanyA1001	Subcontract	CompanyA	Bogus project aa	100 €	Ongoing
6	T604	CompanyD4001	Maincontract	CompanyD	Bogus project bb	200 €	Ongoing
7	T605	CompanyC3001	Maincontract	CompanyC	Bogus project cc	300 €	Ongoing
8	T606	CompanyB2001	Maincontract	CompanyB	Bogus project dd	200 €	Ongoing
9	T607	CompanyA1002	Maincontract	CompanyA	Bogus project ee	200 €	Ongoing
10	T608		Subcontract	CompanyB	Bogus project ff	300 €	Waiting
11	T609		Subcontract	CompanyA	Bogus project dd	300 €	Waiting
12	T610	CompanyD4002	Maincontract	CompanyD	Bogus project ee	250 €	Ongoing
13	T611	CompanyC3002	Maincontract	CompanyC	Bogus project ff	150 €	Ongoing
14	T612	CompanyB2002	Maincontract	CompanyB	Bogus project gg	200 €	Ongoing
15	T613		Maincontract	CompanyA	Bogus project hh
16	T614		Subcontract	CompanyC	Bogus project ii
17	T615		Subcontract	CompanyA	Bogus project jj
18	T616		Maincontract	CompanyD	Bogus project kk	400 €	Waiting
19	T617	CompanyC3003	Maincontract	CompanyC	Bogus project ll	250 €	Ongoing
20	T618		Maincontract	CompanyB	Bogus project mm	100 €	Waiting
21	T619		Maincontract	CompanyA	Bogus project nn
22	T620		Subcontract	CompanyB	Bogus project oo
Tenders

Cell Formulas
Range	Formula
A6:A22	A6	=A5+1

Cells with Data Validation
Cell	Allow	Criteria
C5:C404	List	=Menut!$C$2:$C$5
D5:D404	List	=Menut!$A$2:$A$6
G5:G22	List	=Menut!$B$2:$B$5

Data1 sheet

tender-project-management.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	RAW DATA					REFINED DATA						NEW PROJECT NUMBER CALCULATION
2
3	CompanyA	CompanyB	CompanyC	CompanyD		CompanyA	CompanyB	CompanyC	CompanyD			CompanyA	CompanyB	CompanyC	CompanyD
4	CompanyA1001					CompanyA1001	CompanyB2001	CompanyC3001	CompanyD4001			CompanyA1002	CompanyB2002	CompanyC3003	CompanyD4002
5				CompanyD4001		CompanyA1002	CompanyB2002	CompanyC3002	CompanyD4002			CompanyA	CompanyB	CompanyC	CompanyD
6			CompanyC3001					CompanyC3003				CompanyA1003	CompanyB2003	CompanyC3004	CompanyD4003
7		CompanyB2001
8	CompanyA1002
9
10
11				CompanyD4002
12			CompanyC3002
13		CompanyB2002
14
15
16
17
18			CompanyC3003
19
20
21
Data1

Cell Formulas
Range	Formula
A4:A21	A4	=IF(LEFT(Tenders!B5,8)="CompanyA",Tenders!B5,"")
B4:B21	B4	=IF(LEFT(Tenders!B5,8)="CompanyB",Tenders!B5,"")
C4:C21	C4	=IF(LEFT(Tenders!B5,8)="CompanyC",Tenders!B5,"")
D4:D21	D4	=IF(LEFT(Tenders!B5,8)="CompanyD",Tenders!B5,"")
F4:I21	F4	=IFERROR(INDEX(A$4:A$5002,SMALL(IF((A$4:A$5002<>""),ROW(A$4:A$5002)-ROW(A$4)+1),ROWS(A$4:A4))),"")
L4	L4	=INDEX($F$3:$F$5002,MATCH(TRUE,$F$3:$F$5002="",0)-1)
M4	M4	=INDEX($G$3:$G$5002,MATCH(TRUE,$G$3:$G$5002="",0)-1)
N4	N4	=INDEX($H$3:$H$5002,MATCH(TRUE,$H$3:$H5002="",0)-1)
O4	O4	=INDEX($I$3:$I$5002,MATCH(TRUE,$I$3:$I$5002="",0)-1)
L5:O5	L5	=LEFT(L4,AGGREGATE(15,6,FIND(SEQUENCE(10,,0),L4),1)-1)
L6:O6	L6	=L5&TEXT(SUBSTITUTE(L4,L5,"")+1,REPT(0,LEN(L4)-LEN(L5)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Help please!

 

[tinezi]

UP

 

[tinezi]

Answering to myself. It was easier solution than I first thought...

Even added an automatic datestamp on the right side

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim RelevantArea As Range
    Set RelevantArea = Intersect(Target, Range("G:G"))
    If Not RelevantArea Is Nothing Then
        If Target.Value = "Ongoing" Then
            Application.EnableEvents = False
            Target.Offset(0, 1).Value = Format(Now, "dd.mm.yyyy")
            Application.EnableEvents = True
        End If
             If Range("D" & Target.Row).Value = "CompanyA" Then
                 Application.EnableEvents = False
                 Target.Offset(0, -5).Value = Worksheets("Data1").Range("L6").Value
                 Application.EnableEvents = True
              ElseIf Range("D" & Target.Row).Value = "CompanyB" Then
                 Application.EnableEvents = False
                 Target.Offset(0, -5).Value = Worksheets("Data1").Range("M6").Value
                 Application.EnableEvents = True
              ElseIf Range("D" & Target.Row).Value = "CompanyC" Then
                 Application.EnableEvents = False
                 Target.Offset(0, -5).Value = Worksheets("Data1").Range("N6").Value
                 Application.EnableEvents = True
              ElseIf Range("D" & Target.Row).Value = "CompanyD" Then
                 Application.EnableEvents = False
                 Target.Offset(0, -5).Value = Worksheets("Data1").Range("O6").Value
                 Application.EnableEvents = True
                    End If
    End If
End Sub