How to update values in one column that are linked in a different column?

[benobee]

Hi everyone

I have a query regarding updates to a column listing but unsure where to start with this.

I have a list of applications and servers. There is a multiple server to 1 application relationship.

When I make an update to one column, I would like Excel to automatically update the row wherever a duplicate entry of that application resides.

For example, in the table below, App3 exists on Server C and F. Therefore if I update the value in Cell F4 to Y, I would like to see the value in Cell F7 automatically change to Y. How would this be possible?

Appreciate any advice on this, many thanks!

Book2
	A	B	C	D	E	F
1	Server	Application	Environment	Phase	Country	Complete?
2	A	App1	TEST	EMT-1b	FRANCE	N
3	B	App2	TEST	EMT-1b	FRANCE	N
4	C	App3	TEST	EMT-1b	FRANCE	N
5	D	App4	TEST	EMT-1b	FRANCE	N
6	E	App5	TEST	EMT-1b	FRANCE	N
7	F	App3	TEST	EMT-1b	FRANCE	N
8	G	App7	TEST	EMT-1b	FRANCE	N
Sheet1

 

[MickG]

Hi, Try this:-
Right Click Sheet Tab, Select "View Code", Vb window appears.
Paste code Into Vb window.
Close Vb window.
To Run Code change a Value in column "F".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Column = 6 And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Cells(Target.Row, "B") = Dn.Value And Not Target.Row = Dn.Row [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 4) = Target.Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[benobee]

NICE !!! Cheers Mick.

One further question - if I wanted an additional Column (Column G) which was to do exactly the same thing as Column F, how would I add to this code?

Thinking of having Column G to register when I close down the application.

Many thanks for your help with this so far, this is tremendous help.

Kind Regards

Ben

 

[MickG]

Hi, Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Columns("F:G")) [COLOR="Navy"]Is[/COLOR] Nothing And _
        Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Cells(Target.Row, "B") = Dn.Value And Not Target.Row = Dn.Row [COLOR="Navy"]Then[/COLOR]
                Dn(, Target.Column - 1) = Target.Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick