[olly]

Hi
I have 3 adjacent cells. What i want is, for example, a
way of ensuring that, if there is data present in cell A1
(which looks up data and places product code in B1), then
the user cannot leave cell c1 (order number) blank. The
problem i envisage is that cell A1 looksup product codes
from sheet 2 and places them in B1, so i'm thinking that
any formula to ensure c1 isn't blank must ensure that if
the lookup doesn't find the relevant cell (i.e. a1 isn't a
valid product code and so throws up the messahe "n/a" in
B1) then cell C1 should not be required.

Can anyone help me out here?
Any help greatly appreciated
cheers
olly

[John Wilson]

olly,

Post one of the formulas that you're using and we can probably give you
a better answer.
From what it sounds like you're trying to do, you have a vlookup
formula in B1. You could use something like this in B1:
IF(C1="","Please Fill In Col C",your vlookup formula)

John

Olly wrote:

Hi
I have 3 adjacent cells. What i want is, for example, a
way of ensuring that, if there is data present in cell A1
(which looks up data and places product code in B1), then
the user cannot leave cell c1 (order number) blank. The
problem i envisage is that cell A1 looksup product codes
from sheet 2 and places them in B1, so i'm thinking that
any formula to ensure c1 isn't blank must ensure that if
the lookup doesn't find the relevant cell (i.e. a1 isn't a
valid product code and so throws up the messahe "n/a" in
B1) then cell C1 should not be required.

Can anyone help me out here?
Any help greatly appreciated
cheers
olly

[Tom Ogilvy]

At what point should the user be required to enter a value in B1. After
making an entry in A1 (understand B1 would need to return a valid value -
assume B1 has some type of lookup formula).


Will this only be enforced in A1 - C1 or is it for any value entered in
column A.

Anyway, right click on the sheet tab of the sheet where you want this and
select view code.

then put in a procedure like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim bC1 As Boolean
Static OldRange As Range
On Error GoTo ErrHandler
If Target.Count 1 Then
Set OldRange = Target
Exit Sub
End If
bC1 = False
If OldRange.Address = "$A$1" Or _
OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then
If Not IsError(Range("B1").Value) Then
Application.EnableEvents = False
bC1 = True
Range("C1").Select
End If
End If
ErrHandler:
If bC1 Then
Set OldRange = Range("C1")
Else
Set OldRange = Target
End If
Application.EnableEvents = True
End Sub

lightly tested.


Regards,
Tom Ogilvy

"Olly" wrote in message
...
Hi
I have 3 adjacent cells. What i want is, for example, a
way of ensuring that, if there is data present in cell A1
(which looks up data and places product code in B1), then
the user cannot leave cell c1 (order number) blank. The
problem i envisage is that cell A1 looksup product codes
from sheet 2 and places them in B1, so i'm thinking that
any formula to ensure c1 isn't blank must ensure that if
the lookup doesn't find the relevant cell (i.e. a1 isn't a
valid product code and so throws up the messahe "n/a" in
B1) then cell C1 should not be required.

Can anyone help me out here?
Any help greatly appreciated
cheers
olly

[olly]

The user should be required to enter a value in C1 (not B1-
B1 merely holds a lookup formula, dependent on A1, looking
up the products from sheet2) when there is a valid entry
in A1 ( and therefore B1).

Tom-the code you have put, does it need to be placed in a
module ? Do you have to incorporate it into a button as a
macro ? or does it auto check ?

as for the range, it will be c1 should be mandatory if
valid values in a1 and b1, and c2, if valid entries in a2
and b2.

So is that code suitable for the problem i have described ?
Many thanks for your help
olly


-----Original Message-----
At what point should the user be required to enter a
value in B1. After
making an entry in A1 (understand B1 would need to return
a valid value -
assume B1 has some type of lookup formula).


Will this only be enforced in A1 - C1 or is it for any
value entered in
column A.

Anyway, right click on the sheet tab of the sheet where
you want this and
select view code.

then put in a procedure like this:

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim bC1 As Boolean
Static OldRange As Range
On Error GoTo ErrHandler
If Target.Count 1 Then
Set OldRange = Target
Exit Sub
End If
bC1 = False
If OldRange.Address = "$A$1" Or _
OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then
If Not IsError(Range("B1").Value) Then
Application.EnableEvents = False
bC1 = True
Range("C1").Select
End If
End If
ErrHandler:
If bC1 Then
Set OldRange = Range("C1")
Else
Set OldRange = Target
End If
Application.EnableEvents = True
End Sub

lightly tested.


Regards,
Tom Ogilvy

"Olly" wrote in message
...
Hi
I have 3 adjacent cells. What i want is, for
example, a
way of ensuring that, if there is data present in cell
A1
(which looks up data and places product code in B1),
then
the user cannot leave cell c1 (order number) blank. The
problem i envisage is that cell A1 looksup product codes
from sheet 2 and places them in B1, so i'm thinking that
any formula to ensure c1 isn't blank must ensure that if
the lookup doesn't find the relevant cell (i.e. a1
isn't a
valid product code and so throws up the messahe "n/a" in
B1) then cell C1 should not be required.

Can anyone help me out here?
Any help greatly appreciated
cheers
olly


.

[Tom Ogilvy]

I told you where to put the code. It works each time the selection is
changed as long a macros are enabled and events are enabled. It works for
A1:C1. It checks C1 just like you said - it doesn't do anything to B1.

It doesn't do anything with any other rows, because you didn't say anything
about an other rows.

Regards,
Tom Ogilvy


"olly" wrote in message
...
The user should be required to enter a value in C1 (not B1-
B1 merely holds a lookup formula, dependent on A1, looking
up the products from sheet2) when there is a valid entry
in A1 ( and therefore B1).

Tom-the code you have put, does it need to be placed in a
module ? Do you have to incorporate it into a button as a
macro ? or does it auto check ?

as for the range, it will be c1 should be mandatory if
valid values in a1 and b1, and c2, if valid entries in a2
and b2.

So is that code suitable for the problem i have described ?
Many thanks for your help
olly


-----Original Message-----
At what point should the user be required to enter a
value in B1. After
making an entry in A1 (understand B1 would need to return
a valid value -
assume B1 has some type of lookup formula).


Will this only be enforced in A1 - C1 or is it for any
value entered in
column A.

Anyway, right click on the sheet tab of the sheet where
you want this and
select view code.

then put in a procedure like this:

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim bC1 As Boolean
Static OldRange As Range
On Error GoTo ErrHandler
If Target.Count 1 Then
Set OldRange = Target
Exit Sub
End If
bC1 = False
If OldRange.Address = "$A$1" Or _
OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then
If Not IsError(Range("B1").Value) Then
Application.EnableEvents = False
bC1 = True
Range("C1").Select
End If
End If
ErrHandler:
If bC1 Then
Set OldRange = Range("C1")
Else
Set OldRange = Target
End If
Application.EnableEvents = True
End Sub

lightly tested.


Regards,
Tom Ogilvy

"Olly" wrote in message
...
Hi
I have 3 adjacent cells. What i want is, for
example, a
way of ensuring that, if there is data present in cell
A1
(which looks up data and places product code in B1),
then
the user cannot leave cell c1 (order number) blank. The
problem i envisage is that cell A1 looksup product codes
from sheet 2 and places them in B1, so i'm thinking that
any formula to ensure c1 isn't blank must ensure that if
the lookup doesn't find the relevant cell (i.e. a1
isn't a
valid product code and so throws up the messahe "n/a" in
B1) then cell C1 should not be required.

Can anyone help me out here?
Any help greatly appreciated
cheers
olly


.