Remotely editing cell contents

Shift

New Member
Joined
Sep 12, 2006
Messages
3
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Shift
Welcome to the Board !!!!!

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

kind regards,
Erik
 

Shift

New Member
Joined
Sep 12, 2006
Messages
3
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Sep 12, 2006
Messages
3
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
Code:
c = Sheets(1).Columns(1).Find(c, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
a named range is possible
Code:
    c = Sheets(3).Range("name_list").Find(c, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)

hoping this will get you further
Erik
 

Forum statistics

Threads
1,136,517
Messages
5,676,314
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top