automatic rules in excel to insert value in a cell relative to a reference cell

[desmond_]

Dear Experts,

i wanted to create a timetable in which i only have to insert "x" in a cell and it will automatically insert "xzy" 10 cells to the left/right of where i have entered "x"

is something like this possible ?

here an example of what i mean;
i just need to insert x and xyz will be inserted always 10 cells to the right or left
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]xyz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]xyz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




its probably a very basic function in excel for project management but i couldnt find any information

thanks in advance
Regards dezmond

 

[VHS01]

The following code would go into the code module of the worksheet you are working on.
The Worksheet_Change event is what "automatically" does the work.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Value = "x" Then
Cells(Target.Row, Target.Column + 10).Value = "xyz"
End If
Application.EnableEvents = True
End Sub

 

[desmond_]

Hi,
that works perfectly fine.
do you have a recommendation on how to apply this rule only on a specific range ?

thanks a lot

Regards Dezmond

 

[VHS01]

Just nest the If for Target.value in another If statement.
Choices can be Target.Column, Target.Row, Target.Address, Target.Range
There are lots of great examples on this forum or try online search, you'll quickly find just what you are looking for.
Regards

 

[desmond_]

got it, i have managed to define the range by using Not Intersect(Target, Range)
however, now i end up with the question -> how can i clear the cells if i delete the previously entered "x" ?

if target.value = "" then
Cells(Target.Row,Target.Column +10).Value = ""

doesn't really work (...)
any suggestion for that ?


Regards Des