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

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

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

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



On Mon, 14 Dec 2009 12:42:37 -0800 (PST), Tony wrote:

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

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

On Dec 14, 5:00*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Mon, 14 Dec 2009 12:42:37 -0800 (PST), Tony wrote:
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- Hide quoted text -

- 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,

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

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

On Tue, 15 Dec 2009 12:13:57 -0800 (PST), Tony wrote:

On Dec 14, 5:00*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Mon, 14 Dec 2009 12:42:37 -0800 (PST), Tony wrote:
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- Hide quoted text -

- 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,