change value of cell

Microsoft Excel 2007
January 8, 2010 at 07:58:14
Specs: Windows XP
How can I get the value of a cell to be changed
acording to what was entered.
Eg. I want kind of a lookup table where when I
enter a number for example 1700 into a cell
the value of the same cell will change to a text
like Invalid Account
I type 1700 and it will change to "Invalid


See More: change value of cell

January 8, 2010 at 08:16:38
Data Validation will do what you want.

First you need to make a list of valid numbers.
In cells B1 thru B3 enter numbers 1700, 1800, 1900

Go to cell A1 and Click DATA on the Task Bar (Ribbon)
Click Data Validation (Right Center on ribbon)

In the Allow: window, you need to change the Allow Any
click the small arrow and select List

In the Source window select cells B1 thru B3
Click OK

Now in cell A1 try to enter a number that is not in your list and you get an error message.

You can also modify the error message and create your own by selecting the tabs at the top of the Data Validation window.


Report •

January 8, 2010 at 10:23:45

You can't enter one thing into a cell and then have that same cell display something else.

You can have a lookup table so that you enter a value in one cell and another cell displays the lookup value.
The excel function VLOOKUP() will do that.

If you can be more specific about what you want - is it a one to one relationship
for example 1 returns X, 2 returns Y, 3 returns Z
or is it a range,
for example 1-100 is low 101-200 medium, 201-300 is high

Depending on what you need, there are different ways of using VLOOKUP.


Report •

January 8, 2010 at 15:45:32
You could use a Worksheet_Change macro...

Using the code below, entering anything in A1 other than the values in B1:B30 will cause A1 to display Invalid Account

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   With Range("B1:B30")
    If .Find(Target, lookat:=xlWhole) Is Nothing Then _
        Target = "Invalid Account"
   End With
  End If
End Sub

Report •
Related Solutions

Ask Question