Insert text in one cell based on specific text from another...otherwise leave it it alone

[jmpatrick]

I need a formula that inserts the word SLAB in column E only if the word SLAB appears in column D, otherwise the existing data in column E remains.

I don't want a circular reference, so I can't put a formula in column E (since I need to be able to enter data if SLAB isn't found in D). What about using a helper cell? Can a formula in column F look for the word SLAB in column D, then put the word SLAB in column E, otherwise leave the data in E alone?

 

[Joe4]

I think this is going to require VBA. Is that OK with you?
When do you want this to happen/run?
When someone is entering data in column D, or is all the data already populated, so you would just run it against existing data in column D?

 

[Johnny C]

You found your own solution just use another column. it might be easier to copy col E to F and in col E use

=IF(ISERROR(SEARCH("SLAB",$D[n])),$F[n],"SLAB")

where [n] is the row number

 

[Joe4]

Note that an cell can only contain a hard-coded value OR a formula. You cannot have both, and formulas cannot change that, or the content of OTHER cells. Formulas only control what is returned in the cells that they are contained in.

So if the cells in column E contain text, and you want to change some of the text in those cells based on what is in column D, you must use VBA.
Any workaround changes the nature of that.
It would have return the desired value in some column other than E,
- or-
You would have to initially move the value in column E to another cell so you can place a formula in column E, (like the one Johnny posted).

 

[jmpatrick]

Looks I need VBA. Fortunately, I love me some VBA.

 

[Joe4]

Looks I need VBA. Fortunately, I love me some VBA.

If you need help doing it, please let us know the answers to the question I posted in my first reply.

 

[jmpatrick]

<when do="" you="" want="" this="" to="" happen="" run?="">Thanks!

I want it to happen when "Slab" is entered in D.</when><when someone="" is="" entering="" data="" in="" column="" d,="" or="" all="" the="" already="" populated,="" so="" you="" would="" just="" run="" it="" against="" existing="" d?="">

Some of the cells in E already have data, so if Slab doesn't appear in D (same row) it needs to leave the existing data alone.

Make sense?</when>

 

[Joe4]

Yep, makes perfect sense. We can use a Worksheet_Change event procedure to do that, which runs automatically as data is manually entered in column D.

Right-click on the sheet tab name at the bottom of your sheet, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Intersect(Target, Range("D:D"))
    If Not (myRange Is Nothing) Then
        For Each cell In myRange
            If InStr(LCase(cell), "slab") > 0 Then
                cell.Offset(0, 1) = "Slab"
            End If
        Next cell
    End If

End Sub

This should do what you want.