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.

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.

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.

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 -

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 -

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 -

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