Enter number (data) into cells but want to show different data

[Tony]

Hello,

I want to enter the number into cell but want to show different
result. For example:

Cell (A1), input "1", want to show "1 - Sales forecast"
Cell (A2), input "2", want to show "2 - Sales on-going project"
Cell (A3), input "3", want to show "3 - Sales completed order"
.....

I am wondering if I can setup that in conditional formatting, because
I want to use simple number or alpha-beta in column A for other sumif/
subtotal usage. The input in column A can also use data validation to
pick up from a list.

Thanks.

Tony

 

[Gord Dibben]

Cannot be set up in Conditional Formatting.

You can easily show a text string in a helper cell using VLOOKUP and a
table.

To enter a number in a cell and have that cell value change to a text string
would require VBA.

But the number you entered would no longer be there...........just the text
string.

Is that what you want?

You're talking about using the data for sumif and subtotals.

Here is the event code to change the input numbers to text strings.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
nums = Array(1, 2, 3)
vals = Array("Sales Forecast", "Sales on-going project", _
"Sales completed order")
For Each rr In r
ival = 0
For i = LBound(nums) To UBound(nums)
If rr.Value = nums(i) Then
ival = vals(i)
End If
Next
If ival > 0 Then
rr.Value = ival
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Tony]

Cannot be set up in Conditional Formatting.

You can easily show a text string in a helper cell using VLOOKUP and a
table.

To enter a number in a cell and have that cell value change to a text string
would require VBA.

But the number you entered would no longer be there...........just the text
string.

Is that what you want?

You're talking about using the data for sumif and subtotals.

Here is the event code to change the input numbers to text strings.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10")
If Intersect(Target, r) Is Nothing Then
    Exit Sub
End If
nums = Array(1, 2, 3)
vals = Array("Sales Forecast", "Sales on-going project", _
"Sales completed order")
For Each rr In r
    ival = 0
    For i = LBound(nums) To UBound(nums)
        If rr.Value = nums(i) Then
            ival = vals(i)
        End If
    Next
    If ival > 0 Then
    rr.Value = ival
    End If
Next
End Sub

Gord Dibben  MS Excel MVP








- Show quoted text -

I tried that BV in a test file, it doesn't work....
I created a new spreadsheet and paste the VB code in a new Macro, then
I tried to enter "1" in cell A1, nothing happened.
Wondering if I did something wrong.
Thanks,

 

[Gord Dibben]

Tony

When replying to me please top-post. I hate scrolling down.

The code I posted is sheet event code.

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Alt + q to return to Excel.

Type a number 1 to 3 in A1 to return text string in that cell.

As written it operates on cells A1:A10

You could set the range to A1 only and have a Data Validation dropdown to
select numbers.


Gord