large conditional "if" query

[Tim]

i have data keyed by user into cells in column B, dependent on which column C
should autofill its corresponding cell with one of several things.

if col b cell equal any of the following values i need corresponding column
C cell to show "R2PF"

41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 61, 62, 63,
64, 65, 68, 70, 75, 78

if col b cell equals any of the following values i need corresponding cell
in C to Show "R2FL"

1, 2, 3, 4, 5, 6, 7, 8, 11, 12, 14, 31, 32, 33

if col b equals any of the following values i need corresponding cell in C
to show "R2FF"

69, 71, 72, 73, 74, 76, 77, 84, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99

if col B equals "bulk" i need c to show "CHQU"
if col b equals "CANADA" i need c to show "CANADA"
if col b shows "sample" i need c to show "RPQS"
if col b shows "B-1" i need c to show "RPGK"

also, can the time that column b cell is entered into be stamped into
corresponding cell in column M?

any help greatly appreciated.

 

[Gary''s Student]

First in F1 thru G63 enter:

1 R2FL
2 R2FL
3 R2FL
4 R2FL
5 R2FL
6 R2FL
7 R2FL
8 R2FL
11 R2FL
12 R2FL
14 R2FL
31 R2FL
32 R2FL
33 R2FL
41 R2PF
42 R2PF
43 R2PF
44 R2PF
45 R2PF
46 R2PF
47 R2PF
48 R2PF
49 R2PF
50 R2PF
51 R2PF
52 R2PF
53 R2PF
54 R2PF
55 R2PF
56 R2PF
61 R2PF
62 R2PF
63 R2PF
64 R2PF
65 R2PF
68 R2PF
69 R2FF
70 R2PF
71 R2FF
72 R2FF
73 R2FF
74 R2FF
75 R2PF
76 R2FF
77 R2FF
78 R2PF
84 R2FF
88 R2FF
89 R2FF
90 R2FF
91 R2FF
92 R2FF
93 R2FF
94 R2FF
95 R2FF
96 R2FF
97 R2FF
98 R2FF
99 R2FF
B-1 RPGK
bulk CHQU
Canada Canada
sample RPQS

then in C1 enter:

=VLOOKUP(B1,$F$1:$G$63,2) and copy down.

As data is entered in column B, tab over to column M and enter the time.

 

[Otto Moehrbach]

Tim
I would use a couple of macros to do that. The first macro is a sheet
event macro and must be placed in the sheet module of your sheet. The
second macro can go into a regular module but I would just put both macros
in the sheet module. To access that module, right-click the sheet tab,
select View Code, and paste both macros into that module. "X" out of the
module to return to your sheet. Watch out for line wrapping in this
message. Every line that starts out with "Case" is all in one line until
the next instance of "Case". HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing And Target.Row > 2
Then _
Call FillC(Target)
End Sub

Sub FillC(TheCell As Range)
Dim CValue As String
Select Case UCase(TheCell.Value)
Case 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55,
56, 61, 62, 63, 64, 65, 68, 70, 75, 78: CValue = "R2PF"
Case 1, 2, 3, 4, 5, 6, 7, 8, 11, 12, 14, 31, 32, 33: CValue =
"R2FL"
Case 69, 71, 72, 73, 74, 76, 77, 84, 88, 89, 90, 91, 92, 93, 94,
95, 96, 97, 98, 99: CValue = "R2FF"
Case "BULK": CValue = "CHQU"
Case "CANADA": CValue = "CANADA"
Case "SAMPLE": CValue = "RPQS"
Case "B-1": CValue = "RPGK"
Case Else: CValue = "ERROR"
End Select
TheCell.Offset(, 1).Value = CValue
Cells(TheCell.Row, 13).Value = Now
End Sub

 

[Tim]

I CANT GET EITHER OF THESE SUGGESTIONS TO WORK,

GARYS REQUIRES ME TO FILL INTO OTHER CELLS ALREADY OCCUPIED AND I DONT
UNDERSTAND OTTOS, SORRY.

I AM SURE WAY BACK ON AN OLDER EXCEL I COULD KEY IN ARGUMENTS FOR THINGS
LIKE THIS SIMILAR TO BASIC, E.G, IF B3:B203 = "1" THEN C3 ="R2FL", CAN THIS
BE DONE,

THANKS AGAIN FOR ANY HELP

 

[Dana DeLouis]

Case 41, 42, 43, 44, 45, 46, 47

Just an general idea...

Select Case CLng(Cell.Value)
Case 41 To 56, 61 To 65, 68, 70, 75, 78

 

[Otto Moehrbach]

Gary gave you a solution using formulas. I gave you a programming (VBA)
solution. They both work. Gary gave a column in his explanation. You can
use any available column for his method, even another sheet. HTH Otto