Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
First off, thank you all for your assistance with this question.
I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: M1 N1 O1 P1 Q1 R1 SAT 4 4 2 2.00 100% N/A - - 2 - - I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
Most of what you need to know about Data Validation is right he
http://www.contextures.com/xlDataVal01.html Regards, Ryan-- -- RyGuy "RJ Swain" wrote: First off, thank you all for your assistance with this question. I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: M1 N1 O1 P1 Q1 R1 SAT 4 4 2 2.00 100% N/A - - 2 - - I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
The list I know how to make, I wanted to know if the user was to select
something on the list like N/A, how I can make it wipe out certain values in other cells. "ryguy7272" wrote: Most of what you need to know about Data Validation is right he http://www.contextures.com/xlDataVal01.html Regards, Ryan-- -- RyGuy "RJ Swain" wrote: First off, thank you all for your assistance with this question. I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: M1 N1 O1 P1 Q1 R1 SAT 4 4 2 2.00 100% N/A - - 2 - - I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
A Worksheet Event could handle this. This code goes into the Sheet
module where your data resides. See http://www.rondebruin.nl/code.htm for placement help. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Value = "N/A" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.ClearContents Range(Target.Offset(0, 4), Target.Offset(0, 5)).Cells.ClearContents End If Application.EnableEvents = True End Sub HTH, JP On Feb 8, 3:05*pm, RJ Swain wrote: The list I know how to make, I wanted to know if the user was to select something on the list like N/A, how I can make it wipe out certain values in other cells. "ryguy7272" wrote: Most of what you need to know about Data Validation is right he http://www.contextures.com/xlDataVal01.html Regards, Ryan-- -- RyGuy "RJ Swain" wrote: First off, thank you all for your assistance with this question. I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: * * *M1 * * * * N1 * * * *O1 * * * * P1 * * * *Q1 * * * *R1 * * * * * * *SAT * * * * 4 * * * * *4 * * * * * 2 * * * *2.00 * * 100% * * * * * * *N/A * * * * *- * * * * * - * * * * * 2 * * * * *- * * * * *- I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
What would I need to put in for range?
"JP" wrote: A Worksheet Event could handle this. This code goes into the Sheet module where your data resides. See http://www.rondebruin.nl/code.htm for placement help. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Value = "N/A" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.ClearContents Range(Target.Offset(0, 4), Target.Offset(0, 5)).Cells.ClearContents End If Application.EnableEvents = True End Sub HTH, JP On Feb 8, 3:05 pm, RJ Swain wrote: The list I know how to make, I wanted to know if the user was to select something on the list like N/A, how I can make it wipe out certain values in other cells. "ryguy7272" wrote: Most of what you need to know about Data Validation is right he http://www.contextures.com/xlDataVal01.html Regards, Ryan-- -- RyGuy "RJ Swain" wrote: First off, thank you all for your assistance with this question. I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: M1 N1 O1 P1 Q1 R1 SAT 4 4 2 2.00 100% N/A - - 2 - - I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" .adjust to suit your needs Dim cell As Range If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then On Error GoTo ws_exit: Application.EnableEvents = False If Target.Value = "N/A" Then Range(Target.Offset(0, 1), Target.Offset(0, _ 2)).Cells.ClearContents Range(Target.Offset(0, 4), Target.Offset(0, _ 5)).Cells.ClearContents End If End If ws_exit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Fri, 8 Feb 2008 12:52:11 -0800, RJ Swain wrote: What would I need to put in for range? "JP" wrote: A Worksheet Event could handle this. This code goes into the Sheet module where your data resides. See http://www.rondebruin.nl/code.htm for placement help. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Value = "N/A" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.ClearContents Range(Target.Offset(0, 4), Target.Offset(0, 5)).Cells.ClearContents End If Application.EnableEvents = True End Sub HTH, JP On Feb 8, 3:05 pm, RJ Swain wrote: The list I know how to make, I wanted to know if the user was to select something on the list like N/A, how I can make it wipe out certain values in other cells. "ryguy7272" wrote: Most of what you need to know about Data Validation is right he http://www.contextures.com/xlDataVal01.html Regards, Ryan-- -- RyGuy "RJ Swain" wrote: First off, thank you all for your assistance with this question. I am making a database and in a cell I am using the validation list option for the following selections: SAT, UNSAT, N/A My question is this, if the user selects N/A is there anyway for a set of cells to blank out, so they are not part of another calculation? example: cells: M1 N1 O1 P1 Q1 R1 SAT 4 4 2 2.00 100% N/A - - 2 - - I would like the N/A option from the list to blank out cells so when we calculate percentages the N/A catergories will not affect the overall totals.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
I'm not sure what you mean. You would need to specify what cells you
wanted to clear. I used your sample data above to construct the example -- if you entered "N/A" in M2, it would clear out N2, O2, Q2 and R2. If you posted some of your actual data, someone could be of more specific assistance. --JP On Feb 8, 3:52*pm, RJ Swain wrote: What would I need to put in for range? "JP" wrote: A Worksheet Event could handle this. This code goes into the Sheet module where your data resides. Seehttp://www.rondebruin.nl/code.htm for placement help. Private Sub Worksheet_Change(ByVal Target As Range) * * Application.EnableEvents = False If Target.Value = "N/A" Then * * Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.ClearContents * * Range(Target.Offset(0, 4), Target.Offset(0, 5)).Cells.ClearContents End If * * Application.EnableEvents = True End Sub |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
I found the error, the line needed to be brought up so the command would
work. So it is wiping out the boxes but is the a line I can add that will bring back info if the user selects N/A in error and reselects SAT? "JP" wrote: I'm not sure what you mean. You would need to specify what cells you wanted to clear. I used your sample data above to construct the example -- if you entered "N/A" in M2, it would clear out N2, O2, Q2 and R2. If you posted some of your actual data, someone could be of more specific assistance. --JP On Feb 8, 3:52 pm, RJ Swain wrote: What would I need to put in for range? "JP" wrote: A Worksheet Event could handle this. This code goes into the Sheet module where your data resides. Seehttp://www.rondebruin.nl/code.htm for placement help. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Value = "N/A" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.ClearContents Range(Target.Offset(0, 4), Target.Offset(0, 5)).Cells.ClearContents End If Application.EnableEvents = True End Sub |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Question
Not easily. By the time the Change event fires, the target cell has
already been changed. The event can't tell the difference between when you type "SAT" because you are changing it from "N/A", or when you are selecting it from a previously empty cell. If you wanted an "undo" feature, you would need to store what was currently in those cells in some string variables, then set the cell values back to those string variables' values. (See http://j-walk.com/ss/excel/tips/tip23.htm for more information about undoing.) HTH, JP On Feb 8, 11:50*pm, RJ Swain wrote: I found the error, the line needed to be brought up so the command would work. So it is wiping out the boxes but is the a line I can add that will bring back info if the user selects N/A in error and reselects SAT? "JP" wrote: I'm not sure what you mean. You would need to specify what cells you wanted to clear. I used your sample data above to construct the example -- if you entered "N/A" in M2, it would clear out N2, O2, Q2 and R2. If you posted some of your actual data, someone could be of more specific assistance. --JP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Worksheet Functions | |||
Question on Validation | Excel Worksheet Functions | |||
validation question | Excel Discussion (Misc queries) | |||
Validation Question....Can this be done? | Excel Discussion (Misc queries) |