Excel - Formatting
 

Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
......

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

Take a look at the VLOOKUP / LOOKUP functions in XL help file. I believe one
of them is what you are looking for.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chi" wrote:

Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

In A1:A4 enter the numbers 100, 97.14,94.29,91.42
In B1 enter =0.3498*A1+0.0224
Copy this down the column to get 35, 34, 33,32
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Chi" wrote in message
...
Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

If you put 100 in A1 and 35 in B1 (to establish the factors), then
with your other numbers in A2 down, put this formula in B2:

=ROUND(A2/A$1*B$1,0)

and copy this down as far as you need to. It will result in this:

100 35
97.14 34
94.29 33
91.42 32

Not sure where column C comes into it.

Hope this helps.

Pete

On Sep 9, 5:09*pm, Chi wrote:
Hi,

*Would you please show me how I can format the cells appear like the below
*example?

Ex: On the column A, B and C,

If *I *enter 100, it will *become 35 *
97.14 * *will be * 34
94.29 * * * * * * * *33
91.42 * * * * * * * *32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

I think I posted that divide by 10 code.

Try this for your new problem

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(100, 97.14, 94.29, 91.42) 'edit to suit
nums = Array(35, 34, 33, 32) 'edit to suit
For Each rr In r
inum = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
inum = nums(i)
End If
Next
If inum 0 Then
rr.Value = inum
End If
Next
End Sub

If you have a much longer list of vals and nums we could go to a VLOOKUP
function in the event code.


Gord Dibben MS Excel MVP


On Wed, 9 Sep 2009 11:23:04 -0700, Chi
wrote:

Hi Pete, Bernard and Luke

I am so sorry that my question isn't clear and thanks for all your answers.

I would like to enter 100 in A1, then it returns 35 in cell A1 (same cell)
as soon as I move the cursor to another cell. Here is a list that I would
like to work with:

100 will change to 35
97.14 34
94.29 33
91.42 32
----------------------------------------------
In the past, I had a similar question and got the answer(code) below. It
worked very well. However, this time I have a list of numbers instead of
"what ever I enter divide by 10"
Ex: if I enter 100, it will become 10
If I enter 90, then it become 9

Please fix the code so that I can use it or please advice


Thank you very much!
Chi
-----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Target(1).Address), _
Range("C:C, D:D, E:E")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 10
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.















"Pete_UK" wrote:

If you put 100 in A1 and 35 in B1 (to establish the factors), then
with your other numbers in A2 down, put this formula in B2:

=ROUND(A2/A$1*B$1,0)

and copy this down as far as you need to. It will result in this:

100 35
97.14 34
94.29 33
91.42 32

Not sure where column C comes into it.

Hope this helps.

Pete

On Sep 9, 5:09 pm, Chi wrote:
Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

Hi Gord,

Thank you for answer my question. Yes! You are the one helped me in the
past. Actually, I have a longer list so please also show me how to use the
VLookUp function in the event code. I know to use VLookUp in normal way, but
I am not sure the VLookUp function in the event code. Please advice

I will try your new code and let you know.

Thank you so much!
Chi




"Gord Dibben" wrote:

I think I posted that divide by 10 code.

Try this for your new problem

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(100, 97.14, 94.29, 91.42) 'edit to suit
nums = Array(35, 34, 33, 32) 'edit to suit
For Each rr In r
inum = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
inum = nums(i)
End If
Next
If inum 0 Then
rr.Value = inum
End If
Next
End Sub

If you have a much longer list of vals and nums we could go to a VLOOKUP
function in the event code.


Gord Dibben MS Excel MVP


On Wed, 9 Sep 2009 11:23:04 -0700, Chi
wrote:

Hi Pete, Bernard and Luke

I am so sorry that my question isn't clear and thanks for all your answers.

I would like to enter 100 in A1, then it returns 35 in cell A1 (same cell)
as soon as I move the cursor to another cell. Here is a list that I would
like to work with:

100 will change to 35
97.14 34
94.29 33
91.42 32
----------------------------------------------
In the past, I had a similar question and got the answer(code) below. It
worked very well. However, this time I have a list of numbers instead of
"what ever I enter divide by 10"
Ex: if I enter 100, it will become 10
If I enter 90, then it become 9

Please fix the code so that I can use it or please advice


Thank you very much!
Chi
-----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Target(1).Address), _
Range("C:C, D:D, E:E")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 10
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.















"Pete_UK" wrote:

If you put 100 in A1 and 35 in B1 (to establish the factors), then
with your other numbers in A2 down, put this formula in B2:

=ROUND(A2/A$1*B$1,0)

and copy this down as far as you need to. It will result in this:

100 35
97.14 34
94.29 33
91.42 32

Not sure where column C comes into it.

Hope this helps.

Pete

On Sep 9, 5:09 pm, Chi wrote:
Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

Hi Gord,
Excellent!
Your code works wonderful!

Thanks
Chi

"Gord Dibben" wrote:

I think I posted that divide by 10 code.

Try this for your new problem

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(100, 97.14, 94.29, 91.42) 'edit to suit
nums = Array(35, 34, 33, 32) 'edit to suit
For Each rr In r
inum = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
inum = nums(i)
End If
Next
If inum 0 Then
rr.Value = inum
End If
Next
End Sub

If you have a much longer list of vals and nums we could go to a VLOOKUP
function in the event code.


Gord Dibben MS Excel MVP


On Wed, 9 Sep 2009 11:23:04 -0700, Chi
wrote:

Hi Pete, Bernard and Luke

I am so sorry that my question isn't clear and thanks for all your answers.

I would like to enter 100 in A1, then it returns 35 in cell A1 (same cell)
as soon as I move the cursor to another cell. Here is a list that I would
like to work with:

100 will change to 35
97.14 34
94.29 33
91.42 32
----------------------------------------------
In the past, I had a similar question and got the answer(code) below. It
worked very well. However, this time I have a list of numbers instead of
"what ever I enter divide by 10"
Ex: if I enter 100, it will become 10
If I enter 90, then it become 9

Please fix the code so that I can use it or please advice


Thank you very much!
Chi
-----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Target(1).Address), _
Range("C:C, D:D, E:E")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 10
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.















"Pete_UK" wrote:

If you put 100 in A1 and 35 in B1 (to establish the factors), then
with your other numbers in A2 down, put this formula in B2:

=ROUND(A2/A$1*B$1,0)

and copy this down as far as you need to. It will result in this:

100 35
97.14 34
94.29 33
91.42 32

Not sure where column C comes into it.

Hope this helps.

Pete

On Sep 9, 5:09 pm, Chi wrote:
Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh

Excel - Formatting
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim RR As Range

Set R = Me.Range("A1:A100")
Set Vals = Me.Range("P1:Q100") 'lookup table

'column P has list of possible inputs
'column Q has list of return numbers
'these could be on another worksheet

'Set Vals = Sheets("Sheet3").Range("P1:Q100")

If Intersect(Target, R) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

On Error Resume Next

For Each RR In Intersect(Target, R) 'Only check changed cells
RR = Application.VLookup(RR.Value, Vals, 2, False)
Next RR
endit:
Application.EnableEvents = True

End Sub


Gord

On Wed, 9 Sep 2009 13:16:10 -0700, Chi
wrote:

Hi Gord,

Thank you for answer my question. Yes! You are the one helped me in the
past. Actually, I have a longer list so please also show me how to use the
VLookUp function in the event code. I know to use VLookUp in normal way, but
I am not sure the VLookUp function in the event code. Please advice

I will try your new code and let you know.

Thank you so much!
Chi




"Gord Dibben" wrote:

I think I posted that divide by 10 code.

Try this for your new problem

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(100, 97.14, 94.29, 91.42) 'edit to suit
nums = Array(35, 34, 33, 32) 'edit to suit
For Each rr In r
inum = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
inum = nums(i)
End If
Next
If inum 0 Then
rr.Value = inum
End If
Next
End Sub

If you have a much longer list of vals and nums we could go to a VLOOKUP
function in the event code.


Gord Dibben MS Excel MVP


On Wed, 9 Sep 2009 11:23:04 -0700, Chi
wrote:

Hi Pete, Bernard and Luke

I am so sorry that my question isn't clear and thanks for all your answers.

I would like to enter 100 in A1, then it returns 35 in cell A1 (same cell)
as soon as I move the cursor to another cell. Here is a list that I would
like to work with:

100 will change to 35
97.14 34
94.29 33
91.42 32
----------------------------------------------
In the past, I had a similar question and got the answer(code) below. It
worked very well. However, this time I have a list of numbers instead of
"what ever I enter divide by 10"
Ex: if I enter 100, it will become 10
If I enter 90, then it become 9

Please fix the code so that I can use it or please advice


Thank you very much!
Chi
-----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range(Target(1).Address), _
Range("C:C, D:D, E:E")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 10
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.















"Pete_UK" wrote:

If you put 100 in A1 and 35 in B1 (to establish the factors), then
with your other numbers in A2 down, put this formula in B2:

=ROUND(A2/A$1*B$1,0)

and copy this down as far as you need to. It will result in this:

100 35
97.14 34
94.29 33
91.42 32

Not sure where column C comes into it.

Hope this helps.

Pete

On Sep 9, 5:09 pm, Chi wrote:
Hi,

Would you please show me how I can format the cells appear like the below
example?

Ex: On the column A, B and C,

If I enter 100, it will become 35
97.14 will be 34
94.29 33
91.42 32
.....

Please advice and thanks in advance
Chi Huynh