Excel - Formatting

  • Thread starter Thread starter Chi
  • Start date Start date
C

Chi

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
 
Take a look at the VLOOKUP / LOOKUP functions in XL help file. I believe one
of them is what you are looking for.
 
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
 
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
 
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.
 
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
 
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
 
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
 
Back
Top