Restricting entry in B1 on the basis of entry in A1

[Guest]

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

 

[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

 

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

 

[Guest]

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

 

[Guest]

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

 

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

 

[Guest]

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!

 

[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

 

[Guest]

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

Thanks again!