| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
large conditional "if" query
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. -- Tim |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
large conditional "if" query
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. -- Gary''s Student - gsnu200774 "Tim" wrote: 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. -- Tim |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
large conditional "if" query
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" wrote in message ... 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. -- Tim |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
large conditional "if" query
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 -- Tim "Otto Moehrbach" wrote: 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" wrote in message ... 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. -- Tim |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
large conditional "if" query
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 "Tim" wrote in message ... 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 -- Tim "Otto Moehrbach" wrote: 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" wrote in message ... 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. -- Tim |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
large conditional "if" query
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 -- Dana DeLouis "Otto Moehrbach" wrote in message ... 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" wrote in message ... 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. -- Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
| Utility to "clean up" or "defrag" large Excel file | Excel Discussion (Misc queries) | |||
Hybrid Mode
