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.

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.

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.

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 -

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 -

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 -

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

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

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