Data Valadation or Condittional Formatting??????

[bamaisgreat]

I need for Example: if J4 has data in it then there must be something entered in K4 or a message box will appear saying" MK# Must Be Entered")
and if there is data into K4 there must be data entered into J4 or a message box will appear saying" Drawing Must Be Entered")


Thank you as always

 

[Asala42]

A formula in a nearby cell is an easy approach, though conditional formatting could be used as well.

For example, you could type this formula into cell L4: =IF(len(K4)=0,"<--- Enter the MK# here. Mandatory field","")
Give L4 a red font and obviously use whatever msg you find appropriate. Once data is entered into K4 the msg disappears.

If you really want to buff up the visibility, you could use Conditional formatting directly on K4 as well, for example you could give it a yellow fill and dark border when empty.

 

[bamaisgreat]

Condittional Formatting??????

A formula in a nearby cell is an easy approach, though conditional formatting could be used as well.

For example, you could type this formula into cell L4: =IF(len(K4)=0,"<--- Enter the MK# here. Mandatory field","")
Give L4 a red font and obviously use whatever msg you find appropriate. Once data is entered into K4 the msg disappears.

If you really want to buff up the visibility, you could use Conditional formatting directly on K4 as well, for example you could give it a yellow fill and dark border when empty.

That works well but I believe it will need to be done in conditional formatting. The reason is that I need to make the cell have to contain data.. Could you lead me on this?? Thanks

 

[Asala42]

Re: Condittional Formatting??????

It sounds like you're looking to find a way to force the data entry peeps to enter values into those cells. I suppose one thing you could do is apply conditional formatting to the <rest> of the form, blacking out these cells (black fill & text) or whiting out them (set the text and borders to match the background), until both K4 and J4 have entries. The idea is to prevent the user from filling out the rest of the form until they've entered values in those cells.

To illustrate:
-Select a large range below the form
-Format>Conditional formatting
-use a formula: =OR(LEN($J$4)=0,LEN($L$4)=0)
-Format (black fill/black text)

Now there are also VBA approaches. (Forcing an actual message box on opening the file for example). Both methods have their flaws and can be bypassed by determined users.

 

[bamaisgreat]

Re: Condittional Formatting??????

Thanks for the info. I need it to be pretty cut and dry which ever approach. Just simply J4 and K4 cannot be empty, if one caontains data the other one has to.