How to reference a cell that contains a word to into a cell that has a sentence

Requirement:

Is it possible to reference a cell that contains a word to into a cell that has a sentence?

For example:
Sheet 1
Cell A1: Dog

Sheet 2
Cell A1: This animal is a _____ and....
I'd like to insert whatever is in Sheet1 Cell A1 into this blank that's part of Sheet2 Cell A1.

Solution:

There are two ways you can do this:

In Sheet 2 A1 place the following

="This animal is a " & Sheet1!A1 & " and..."

If you are willing to use VBA, place the following macro in the code module of Sheet1.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range
    Set VRange = Range("A1")
    If Union(Target, VRange).Address = VRange.Address Then _
        Sheets("Sheet2").Range("A1") = "This animal is a " & _
        VRange.Value & " and ..."
End Sub

To do this, copy the code above and then right click on the Sheet1 tab and select View Code.

Paste in the copied code (and if necessary erase any empty Sub routines that got created by opening the code window -- one that has only two lines -- Sub ... and End Sub.

Also make sure that you have only one Private Sub Worksheet_Change sub in this workbook code window.

Incidentally, if you inserting a number instead of text, you could use CUSTOM FORMATTING on the target cell to do a similar thing much more easily -- but I don't think this works for text.

Obtained from the OzGrid Help Forum.

Solution provided by Will Riley and tomach.