Remotely editing cell contents

[Shift]

This may be simple as heck, but I cannot figure out ,or find a way, to edit a cell from another cell.

Copying contents to a cell is easy enough (ie: put "=A2" in cell A1)
But how do I reference it the other way around? Like MAKE the contents in cell A1 = A2 FROM cell A2?

Killing me on this one. Replace() doesnt work.

Any help would be great.

 

[erik.van.geit]

Hi, Shift
Welcome to the Board !!!!!

do you mean
copy A2
rightclick A1
PastSpecial
click button "pastelink"

kind regards,
Erik

 

[Shift]

Hey thanks, good to be here.

Actually I'm not sure if that applies to my need, I dont fully understand linking cells.

I'll define a little more of what Im tryign to do.

I want to put in a conditional statement, in cell A3, that says if (for example) cell A1 = "Ramon", then I want to change the contents of A1 to "RR", otherwise do nothing.

I want there to be no user connection to the change, just the initial entry, controlled by validation.

Thank you for your quick response.

 

[pgc01]

Hi Shift
Welcome to the board

A formula is just a way to specify the cell's value. A formula in A3 defines the value of A3. A formula in A3 will not alter the value of A1.

If based on a value of one cell you want to change another cell you have to use vba, for ex. a change event.

Hope this helps
PGC

 

[erik.van.geit]

you're WELCOME

what you ask there is not logic
if the contents of A1 = "Ramon" it cannot be changed to "RR" using a formula: you would need code to do that

do you mean this:
type "Ramon" in A1
change immediately to "RR"

perhaps explaining a bit more what you want to do, will give us more ideas to come with a solution

greetings,
Erik

 

[Shift]

I was kind of thinking I'd ahve to code it, learning all this as I go really.

But yes you are right, I wanted to enter "Ramon" into a field and have it change immediately to "RR". Please tell me you know of a simple way to do this. Simple or otherwise I catch on pretty quick.

Its a field that someone would enter in a persons name, but for space saving I want it to convert to thier initials. So that we dont have to deal with anyone who might not be familiar with everyones names; and spelling. I was going to use a validation list to keep it simple and exact. Then compare that input to a table of names and convert the input name to its associated initials.

Theres more tweaking than that, but thats the jist of it.

 

[erik.van.geit]

say, you have a table like this

  A             B        
1 fullname      initials 
2 Ramon Ramirez RR       
3 Pete Simple   PS       
4 John Doe      JD       
5 Paul Yank     PY       

test
[Table-It] version 06 by Erik Van Geit

some sheetcode would do what you want
I restricted the range to C2:C100, but you can change it to anything you want as long as the table itself is not included

in the sheetmodule

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changeRange As Range
Dim checkRange As Range
Dim c As Range
Dim found As Range

Set checkRange = Range("C2:C100")

Set changeRange = Intersect(Target, checkRange)
If changeRange Is Nothing Then Exit Sub

Application.EnableEvents = False

    For Each c In changeRange
        If c <> "" Then
        Set found = Nothing
        Set found = Columns(1).Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not found Is Nothing Then c = found.Offset(0, 1)
        End If
    Next c

Application.EnableEvents = True

End Sub

the table could be on another sheet
just edit the references

c = Sheets(1).Columns(1).Find(c, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)

a named range is possible

    c = Sheets(3).Range("name_list").Find(c, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)

hoping this will get you further
Erik