Add a Text when a Cell is filled?

[Appleboy]

Hi there,

I am wondering if this is possible,

If I have a small table, the table contain data and in B5 of the table data it is filled with a data value (e.g 5000). When B5 is filled with 5000, in the cell A1, a text will auto fill and put DONE.

If B5 is empty, then cell A1 will have nothing there.

Is it possible and how should I approach this? Thanks!

 

[venkat1926]

right click the sheet tab and click view code
in the window that comes up copy this event code
and save the file

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$5" Then Exit Sub
If Target = 5000 Then
Range("A1") = "done"
Else
Range("A1") = ""
End If
End Sub

now go to sheet in B5 enter 5000 and see what happens to A1
now either type some other number in B5 and clear that cell . what happens to A1

 

[Appleboy]

Thanks for the macro code, the 5000 will actually changes depending on the new data putting in. In addition, the whole data table will need to be coded too in this case for example B5 to G7 range. Hence I think I can't use the fixed B5 = 5000 thing.

Lastly, is it possible to do this without using macro?

 

[venkat1926]

on second thought yes it can be done and easier

in A1 copy this formula

=IF(B5=5000,"done","")

and hit enter
save the file

now do your experiments

 

[Appleboy]

Thanks, this works. However I am wondering, is it possible to also invert the roles?

Instead of placing the formula in A1, I place the formula in B5. So when B5 has 5000 or any other numbers filled, a text DONE will send it to A1. This means A1 cell will contain no formula at all.

Again, without the usage of macro. Can this be done?

 

[venkat1926]

my first solution

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$B$5" Then Exit Sub If Target = 5000 Then Range("A1") = "done" Else Range("A1") = "" End If End Sub

is not a macro but an event code. once you park it as I told you and save the file, you need not run the macro every time. whenever B5 is filled with some entry automatically a1 will give you "done" or a blank cell.

a formula in a cell talk about what will happen to THAT cell (not some other cell) when condition is satisfied.

if you explain why you do not want a macro or an event code , it is possible to think of some other solution

 

[venkat1926]

if your source cell is anywhere in B5 to G7 the event code can be modified.
park it and save the file

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 5 Or Target.Row > 7 Then Exit Sub
If Target.Column < Range("B1").Column Or Target.Column > Range("G1").Column Then Exit Sub
If Target = 5000 Then
Range("A1") = "done"
Else
Range("A1") = ""
End If
End Sub

 

[Peter_SSs]

I am wondering, is it possible to also invert the roles?

Instead of placing the formula in A1, I place the formula in B5. So when B5 has 5000 or any other numbers filled, a text DONE will send it to A1. This means A1 cell will contain no formula at all.

Again, without the usage of macro. Can this be done?

No, not without vba. Two reasons ..

1. B5 cannot contain a formula and an entered value. If B5 contains a formula and you enter say 5000 in B5, it no longer contains a formula.

2. A formula cannot 'send' a value to another cell. A formula is placed in a cell and the result of that formula appears in that same cell, nowhere else.

 

[Appleboy]

Ar I see, ****! Thanks for the help guys.

Using the event macro above will be a hassle because my table will always be expanding, this means I will need to head back into the VBA and update on the table range.

 

[Peter_SSs]

Using the event macro above will be a hassle because my table will always be expanding, this means I will need to head back into the VBA and update on the table range.

The event code should be able to be modified to handle a changing table. However, I do not really understand what you want.

Can you explain again in more detail?

What version of Excel are you using?

Tell us more about the table. For example ..
- Does it have a name?
- What is the upper left cell?
- Does it have a fixed number of columns? If so, how many?

Perhaps you could even post a small set of dummy data to help illustrate what is happening?

The more we know about what you have and what you want to achieve, the better chance of a good solution.