OzGrid

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

< Back to Search results

 Category: [Excel]  Demo Available 

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.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to change reference columns in another worksheet using VBA looping
How to use VBA code using relative references
How to use VBA code to reference cell to another sheet
How to use cell content as input to a structured reference as part of a lookup function

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)