A method without 'if' is:
Using column X1 to X11 and Y1 to Y11 as helper columns, if you enter
the LOW range in column X, and the relavant CODE in column Y you could,
for data entered in A1 to A10, enter in B1
=VLOOKUP(A1,X$1:Y$11,2,TRUE)
and formula-drag that to B10 (the end of your data.
This will set code 4 for a value of 17 in your sample data, and will
enable you to amend or add to the lookup range quite easily should you
need to expand the range out to (say) 300. (remember to keep column X
sorted).
Hope this helps.
Max Wrote:
One way via formulas ..
Assuming this reference table is in Sheet1, A1:C12
code low high
1 0 5
2 6 10
3 11 15
4 16 20
5 21 25
6 16 30
7 31 35
8 36 40
9 41 45
10 46 50
11 51 55
In say, Sheet2, if values will be entered in A1:A10
you could put in B1:
=IF(A1="","",IF(OR(A1<0,A155),"out-of-range",INDEX(Sheet1!A:A,MATCH(A1,Shee
t1!B:B,1))))
then just copy B1 down to B10
Col B will return the desired code values from Sheet1 for the inputs in
col
A
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Vika" wrote in message
...
I need an If Then statement that will take entered value from A1:A10
and
compares it through "low" and "high" range:
code low high
1 0 5
2 6 10
3 11 15
4 16 20
5 21 25
6 16 30
7 31 35
8 36 40
9 41 45
10 46 50
11 51 55
And then assigns correct code to cell B1:B10. For example if the
entered value in cell A1 is 5, it will automatically enter code 1 to
cell B1.
The If Then statement has to take value from A1:A2 and assign code
to
B1:B10.
I was thinking to do it this way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] 0 And [A1] <= 5 Then
[B1] = 1
ElseIf [A1] <= 10 And [A1] = 6 Then
[B1] = 2
ElseIf [A1] <= 15 And [A1] = 11 Then
[B1] = 3
ElseIf [A1] <= 20 And [A1] = 16 Then
[B1] = 4
ElseIf [A1] <= 25 And [A1] = 21 Then
[B1] = 6
ElseIf [A1] <= 30 And [A1] = 26 Then
[B1] = 7
ElseIf [A1] <= 35 And [A1] = 31 Then
[B1] = 8
ElseIf [A1] <= 40 And [A1] = 36 Then
[B1] = 9
ElseIf [A1] <= 45 And [A1] = 41 Then
[B1] = 10
ElseIf [A1] <= 50 And [A1] = 46 Then
[B1] = 11
ElseIf Target <= 55 And [A1] = 51 Then
[B1] = 12
End If
End Sub
But this way I end up with a long code for each cell. Is there any
other way?
Thank you for any help.
--
Vika
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=395631