Is there a way, using formulas, or validation settings to restrict entry in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

If the data in Column B is strictly for *display only* (no calculation
needed), then you might try this:

Say you're looking to configure B1 to B25,

Select the range B1:B25, then:
<Data <Validation <Settings tab,
Under Allow, click on "List",
Under Source, enter:
High,Mod
Then <OK

While the range is *still* selected, click,
<Format <Conditional Formatting
Change "Cell Value Is",
To "Formula Is",
And enter this in the box to the right:

=A1<3

Then click on "Format", and the "Font" tab,
Expand the Color box, and choose the White,
Then <OK <OK.

This should give you the restricted display you're looking for.

Don't forget though, the data choices *will* exist in Column B, but will not
be visible, meaning that you *cannot* reference Column B in any formulas.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Restrict entry to 2 options,

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

Hiya.. this was an extremely creative solution, and would have worked great -
but I will be involving columb B in reporting.. unfortunately.. so I can't do
it.. :-(

"Ragdyer" wrote:

If the data in Column B is strictly for *display only* (no calculation
needed), then you might try this:

Say you're looking to configure B1 to B25,

Select the range B1:B25, then:
<Data <Validation <Settings tab,
Under Allow, click on "List",
Under Source, enter:
High,Mod
Then <OK

While the range is *still* selected, click,
<Format <Conditional Formatting
Change "Cell Value Is",
To "Formula Is",
And enter this in the box to the right:

=A1<3

Then click on "Format", and the "Font" tab,
Expand the Color box, and choose the White,
Then <OK <OK.

This should give you the restricted display you're looking for.

Don't forget though, the data choices *will* exist in Column B, but will not
be visible, meaning that you *cannot* reference Column B in any formulas.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Restrict entry to 2 options,

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

WOW this is like MAGIC!!! It WORKS! It WORKS!

Now I have a sub question... Is there a way to have the High and Med appear
as drop down choices to prevent misspellings..extra spaces..etc.?

"Biff" wrote:

Hi!

Select the range of cells you want to validate.
Goto DataValidation
Allow: Custom
Formula: =AND(ISNUMBER(A1),A12,OR(B1="High",B1="Mod"))
Uncheck: Ignore blank
OK

Biff

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

How exactly are you using the contents of Column B in your reporting?

Are you perhaps counting the Highs and Mods?

If you're using something like this:

=Countif(B1:B25,"High")
=Countif(B1:B25,"Mod")

You could return the *same* results using something like this:

=SUMPRODUCT((A1:A25=3)*(B1:B25="High"))
=SUMPRODUCT((A1:A25=3)*(B1:B25="Mod"))

These formulas would allow you to display the data as you wish, and at the
same time accurately count the "apparent" results.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Stilla" wrote in message
...
Hiya.. this was an extremely creative solution, and would have worked
great -
but I will be involving columb B in reporting.. unfortunately.. so I can't
do
it.. :-(

"Ragdyer" wrote:

If the data in Column B is strictly for *display only* (no calculation
needed), then you might try this:

Say you're looking to configure B1 to B25,

Select the range B1:B25, then:
<Data <Validation <Settings tab,
Under Allow, click on "List",
Under Source, enter:
High,Mod
Then <OK

While the range is *still* selected, click,
<Format <Conditional Formatting
Change "Cell Value Is",
To "Formula Is",
And enter this in the box to the right:

=A1<3

Then click on "Format", and the "Font" tab,
Expand the Color box, and choose the White,
Then <OK <OK.

This should give you the restricted display you're looking for.

Don't forget though, the data choices *will* exist in Column B, but will
not
be visible, meaning that you *cannot* reference Column B in any formulas.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

Restrict entry to 2 options,

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT
to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other
ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

Hmm yes..this might work. Actually, first, a summary last tab will
automatically reflect what's entered in the other tabs, then I'm compiling
all the summary tabs into one large database. What I could do, is let them
be summarized and then use a simple filter to erase all the entries in B when
the values in A are less than 3.

Hmm.. .thanks! This might work too!

"RagDyeR" wrote:

How exactly are you using the contents of Column B in your reporting?

Are you perhaps counting the Highs and Mods?

If you're using something like this:

=Countif(B1:B25,"High")
=Countif(B1:B25,"Mod")

You could return the *same* results using something like this:

=SUMPRODUCT((A1:A25=3)*(B1:B25="High"))
=SUMPRODUCT((A1:A25=3)*(B1:B25="Mod"))

These formulas would allow you to display the data as you wish, and at the
same time accurately count the "apparent" results.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Stilla" wrote in message
...
Hiya.. this was an extremely creative solution, and would have worked
great -
but I will be involving columb B in reporting.. unfortunately.. so I can't
do
it.. :-(

"Ragdyer" wrote:

If the data in Column B is strictly for *display only* (no calculation
needed), then you might try this:

Say you're looking to configure B1 to B25,

Select the range B1:B25, then:
<Data <Validation <Settings tab,
Under Allow, click on "List",
Under Source, enter:
High,Mod
Then <OK

While the range is *still* selected, click,
<Format <Conditional Formatting
Change "Cell Value Is",
To "Formula Is",
And enter this in the box to the right:

=A1<3

Then click on "Format", and the "Font" tab,
Expand the Color box, and choose the White,
Then <OK <OK.

This should give you the restricted display you're looking for.

Don't forget though, the data choices *will* exist in Column B, but will
not
be visible, meaning that you *cannot* reference Column B in any formulas.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

Restrict entry to 2 options,

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT
to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other
ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

Hi!

Unfortunately, you can have only one type of validation at a time.

Misspellings and extra spaces and the like will be prevented based on what
values you use in the formula.

If you use this formula and A12:

=AND(ISNUMBER(A1),A12,OR(B1="High",B1="Mod"))

The only entries that can be made are either "High" or "Mod", that's it,
nothing else.**

Not: <spaceHigh, or High<space, or Hihg, or Mdo

** data validation CAN be defeated by dragging or copy/pasting. There's
nothing you can do about that. That's just the way it is! Also, it's not
case sensitive unless you specifically write the formula to check for case.

You could do what you want with the drop down but that would require VBA
programming. I can't help with that.

Biff

"Stilla" wrote in message
...
WOW this is like MAGIC!!! It WORKS! It WORKS!

Now I have a sub question... Is there a way to have the High and Med
appear
as drop down choices to prevent misspellings..extra spaces..etc.?

"Biff" wrote:

Hi!

Select the range of cells you want to validate.
Goto DataValidation
Allow: Custom
Formula: =AND(ISNUMBER(A1),A12,OR(B1="High",B1="Mod"))
Uncheck: Ignore blank
OK

Biff

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict
entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT
to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other
ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!

Thanks Biff.. yeah, I understand. I think this is a great solution anyway,
and it will solve my problem.

Thanks again!

"Biff" wrote:

Hi!

Unfortunately, you can have only one type of validation at a time.

Misspellings and extra spaces and the like will be prevented based on what
values you use in the formula.

If you use this formula and A12:

=AND(ISNUMBER(A1),A12,OR(B1="High",B1="Mod"))

The only entries that can be made are either "High" or "Mod", that's it,
nothing else.**

Not: <spaceHigh, or High<space, or Hihg, or Mdo

** data validation CAN be defeated by dragging or copy/pasting. There's
nothing you can do about that. That's just the way it is! Also, it's not
case sensitive unless you specifically write the formula to check for case.

You could do what you want with the drop down but that would require VBA
programming. I can't help with that.

Biff

"Stilla" wrote in message
...
WOW this is like MAGIC!!! It WORKS! It WORKS!

Now I have a sub question... Is there a way to have the High and Med
appear
as drop down choices to prevent misspellings..extra spaces..etc.?

"Biff" wrote:

Hi!

Select the range of cells you want to validate.
Goto DataValidation
Allow: Custom
Formula: =AND(ISNUMBER(A1),A12,OR(B1="High",B1="Mod"))
Uncheck: Ignore blank
OK

Biff

"Stilla" wrote in message
...
Is there a way, using formulas, or validation settings to restrict
entry
in
one cell based on what is entered in other cells?

Example. If values in Col A are less than 3, I would like the user NOT
to
be able to enter ANYTHING IN col B. IF values in Col A are 3 or more
however, I would like the user to have a choice of two other
ratings...say
High, Moderate. so that final data could look something like this:

A B
4 High
1 BLANK (this cell would be restricted)
5 Mod
2 BLANK (this cell would be restricted)
3 High
4 Mod
5 Mod

THank You!!!