Restricting entry in B1 on the basis of entry in A1

S

Stilla

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!!!
 
B

Biff

Hi!

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

Biff
 
R

Ragdyer

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.
 
S

Stilla

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.. :-(
 
S

Stilla

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.?
 
R

RagDyeR

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!
=====================================================

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.. :-(
 
S

Stilla

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!
 
B

Biff

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 A1>2:

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

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

Not: <space>High, 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
 
S

Stilla

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

Thanks again!
 
Top