Command cell from another cell

[ExNub]

Hello everyone!

I just registered in this forum, I'm sorry I didn't make an introduction yet but ill leave here a little intro.

I never used Excel before, only to see some data or ultra basic things. Now I'm creating a excel system to control sales as a performance guide.

I need help in a little question.

Obviously in excel we can get a value from another cell... if I want to get a value from A1 i might just do in a random cell =A1, so far so good...

My question here is... Is there a opposite way?

Can I "write" in A1 from a random cell?

And more important... Using formulas only?



I'm sorry to come here and just ask, don't want to be rude, i'm just a little desperate.

Thank you in advance.

[PFDave]

Hi, you can't put random contents into a cell, you are telling the cell what you would like it to display.

Why would you want random values in cells?

I think we need a bit more detail or better still an example spreadsheet displaying what you would like to see

[ExNub]

No no friend... No random value.

I was trying to say "pick a random cell" or "no specific cell".

Basically... You can read a cell from another cell, but can you "write" in a cell from another cell?

[PFDave]

If you have the value =A1 placed into B1 and then typed into A1 you will see the contents of B1 change. Is this what you are querying?

[ExNub]

I'm asking for a way (if there is any) to command in B1 to change a value in A1.5wglrn.jpg

EDITED: A1 has no values, what I want it to create a formula that when added to anywhere in the spreadsheet (In this image we can see is B1), it will add a value to A1. What I want resembles a little object oriented programming.

[ImranBhatti]

Put this in A1

=IF(C1<>"",C1,"")

and type something in C1

[ExNub]

Put this in A1


and type something in C1

This is almost the same as the =C1

What I need is to add data to A1 from any other cell, some formula that can point the cell and remote edit her.

[sandy666]

you mean: B1: =A(randomnumber) ?

[ImranBhatti]

O now I understand
"You want not to enter any formula in A1 and it should be populate by populating any other cell."

[sandy666]

you can try eg. =INDIRECT("A"&RANDBETWEEN(1,10))
where your data is in A1: A10

or if you want randomize A1:E10 you can try: =INDIRECT(CHAR(RANDBETWEEN(65,69))&RANDBETWEEN(1,10))

[ExNub]

Sorry for my late response


This is not what I need but it might become handy for something else, thank you!

=INDIRECT("A"&RANDBETWEEN(1,10))

This:

O now I understand
"You want not to enter any formula in A1 and it should be populate by populating any other cell."

Correct! Sorry If I was confusing explaining :D
If you can translate that into a formula... I've been struggling with this for a while and and can't find anything resembling this in google or anyplace in the web.

[Kaper]

To answer open way: No, you can't achieve this by a formula.
but changes in a sheet can be reflected in a cell.

See how such sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells(1, 1).Value <> "" Then Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = "value: " & Target.Cells(1, 1).Value & " in: " & Target.Address(0, 0) & " written: " & Format(Now, "hh:mm:ss")
Application.EnableEvents = True
End Sub

works (see the code by rightclicking a tab with sheet name and select show code from pop-up menu).

[ExNub]

To answer open way: No, you can't achieve this by a formula.
but changes in a sheet can be reflected in a cell.

See how such sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells(1, 1).Value <> "" Then Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = "value: " & Target.Cells(1, 1).Value & " in: " & Target.Address(0, 0) & " written: " & Format(Now, "hh:mm:ss")
Application.EnableEvents = True
End Sub

works (see the code by rightclicking a tab with sheet name and select show code from pop-up menu).

That's what I was afraid I was trying to run away from VBA. I belive I can modify that code to blend in to what I need. Thank you Kaper and everyone else who was here to help.

[Kaper]

Well, you can think of a cell referring not to another cell, but to a name (Ctrl+F3) . And the name can be not referring to a cell, but be calculated as a formula. But basically - it is still a cell containing

=YourName

so not too far from simple

=IF(C1<>"",C1,"") 

[ExNub]

Thank you everyone! I have my problem solved! I found another way to work things out. You can close this thread!

[ImranBhatti]

Thank you everyone! I have my problem solved! I found another way to work things out. You can close this thread!

and would you like to share that work around with the community?

[sandy666]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.