Move Cell Value to Right Using Formula

S

sven.halb

I'm trying to do the following:

If A1 = 2, then starting at position C5, move 2 cells to the right and
populate with a value
If A1 = 3, then starting at position C5, move 3 cells to the right and
populate with a value
etc...

Appreciate any ideas

Thanks,
Sven
 
M

Mike

the cells to the right of C5 are E5 & F5 so put a formula in those cells

for E5

=IF(A1=2,"Some value","")

for F5
=IF(A1=3,"Some value","")

Mike
 
D

Don Guillett

Best done with a worksheet_change macro using an if or selectcase statement
within
 
D

Don Guillett

right click sheet tab>view code>insert this>see how it works and change to
suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Target.Offset(, Target.Value) = Target * Target

End Sub
 
G

Gord Dibben

Formulas cannot move things, only return results to the cell in which they
reside.

Either have formulas in E5 and F5 like =IF(A1=2,"value for 2","not 2")

Or go with event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value

Case 2
Target.Offset(5, 4).Value = "value for 2"

Case 3
Target.Offset(5, 5).Value = "value for 3"

End Select
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above code into that sheet module.


Gord Dibben MS Excel MVP
 
Top