Prevent user input, if adjacent cell text is identical

[Mik]

My worksheet has cells that contain text - Such as 'INF', 'STO', and
'BAC' etc...
Some of these cells are merged into as many as 9 cells (vertically).

I am trying to prevent the same text being input into the adjacent
single or merged cell (which is offset to the right by ONE cell).

So, if a merged cell comprises of ("D510") and contains the text
'STO',
I want to prevent the user from entering 'STO' in ("E5:E10") etc...

The cells can contain other text, but i want to prevent only 'INF',
'STO', and 'BAC' from being input. All other text is OK.

The cells are currently populated by a Userform ComboBox1.

Can anybody please help?

Thanks
Mik

 

[CB]

I'm not sure if this will work in your situation (with merged cells &
ComboBox...etc)

Try using Data - Validation - Custom [then enter formula: if(a1=b1)]
You can then customize the Input Message & Error Alert message.

Kind Regards
Charlie.B

 

[Mik]

I'm not sure if this will work in your situation (with merged cells &
ComboBox...etc)

Try using Data - Validation - Custom [then enter formula: if(a1=b1)]
You can then customize the Input Message & Error Alert message.

Kind Regards
Charlie.B

My worksheet has cells that contain text - Such as 'INF', 'STO', and
'BAC' etc...
Some of these cells are merged into as many as 9 cells (vertically).

I am trying to prevent the same text being input into the adjacent
single or merged cell (which is offset to the right by ONE cell).

So, if a merged cell comprises of ("D510") and contains the text
'STO',
I want to prevent the user from entering 'STO' in ("E5:E10") etc...

The cells can contain other text, but i want to prevent only 'INF',
'STO', and 'BAC' from being input. All other text is OK.

The cells are currently populated by a Userform ComboBox1.

Can anybody please help?

Thanks
Mik- Hide quoted text -

- Show quoted text -

Thanks for the reply, but unfortunately this approach doesn't appear
to work.

Any other ideas?

Mik.

 

[GS]

Mik expressed precisely :

My worksheet has cells that contain text - Such as 'INF', 'STO', and
'BAC' etc...
Some of these cells are merged into as many as 9 cells (vertically).

I am trying to prevent the same text being input into the adjacent
single or merged cell (which is offset to the right by ONE cell).

So, if a merged cell comprises of ("D510") and contains the text
'STO',
I want to prevent the user from entering 'STO' in ("E5:E10") etc...

The cells can contain other text, but i want to prevent only 'INF',
'STO', and 'BAC' from being input. All other text is OK.

The cells are currently populated by a Userform ComboBox1.

Can anybody please help?

Thanks
Mik

This worked for me in $B$1:$G$5 using DV (Data Validation) as follows:
Select the cells that you want to disallow duplicate entry.
Open the DV dialog. (Data>Validation...)
On the Criteria tab:
In the Allow box choose 'Custom'.
In the formula box enter '=B1<>$A1'

Note that with merged cells the reference is the topmost cell on the
left. In the case of a single column vertical merge, this is the top
cell in the merged cells. For example, if you merge $A$1:$A$5 then $A$1
is the reference address for that group of cells. Likewise, $E$1 is the
ref address for $E$1:$E$5. so even though the merged group contains 5
cells, their ref address is always the first cell. When entering the
formula in Data Validation, use this concept as shown above.

Note also that I specifically entered the formula with '$A1' being
column absolute, row relative. I entered 'B1' as column relative, row
relative. This make column 'A' the reference no matter what other
columns receive data. IOW, the cells with the DV criteria shown will
not be able to contain the same value as any cell in column 'A'. this
only applies if they are merged same as $A$1:$A$5. If you want to
prevent duplication in single cells AND/OR merged cells then you have
to enter the formula as follows:
=B1<>$A$1
This will prevent the value in $A$1 from being entered in any cell of
any column where you applied this DV.

HTH
Garry

 

[Rick Rothstein]

I'm guessing that since you posted in a programming newsgroup, that a VB
solution would be acceptable. Before giving you code, however, can you
clarify a couple of things please? First, are merged cells in your columns
"paired"? That is, if D510 are merged, then is E5:E10 also merged, or can
E5:E10 be composed of individual cells (or possibly cells merged in a
different pattern than those adjacent to it)? Second, are Columns D and E
the only columns that will have the relationship you described or are there
other paired columns that must be considered?

 

[GS]

Rick Rothstein pretended :

I'm guessing that since you posted in a programming newsgroup, that a VB
solution would be acceptable. Before giving you code, however, can you
clarify a couple of things please? First, are merged cells in your columns
"paired"? That is, if D510 are merged, then is E5:E10 also merged, or can
E5:E10 be composed of individual cells (or possibly cells merged in a
different pattern than those adjacent to it)? Second, are Columns D and E the
only columns that will have the relationship you described or are there other
paired columns that must be considered?

Hi Rick,
It occured to me to redirect the OP to an Excel group due to him only
stating ref to "my worksheet...", and suspected he was not looking for
a VB[A} solution. I did not do that because I realized that if Data
Validation was needed 'after the fact' then code may be required to
apply that conditionally.

The OP implies that he has control over the design/structure of the
worksheet (vsv "my worksheet..."), and so IMO DV is the way to go. How
that's implemented will be conditional on if the source (containing the
criteria) OR target (restricted) cells are merged or not, and so is why
I gave 2 examples of the DV formula to use. In this case, the code we
suggest needs to evaluate both conditions to determine what formula to
apply. AFAICT, the determing factor for the target cells is whether the
source cells are merged or not, as to what DV should be applied based
on two things: their relative location to the merged source, and if
they are merged, single, or a mix of both.

Otherwise, I suspect that single source cells would be handled row by
row with a column-absolute, row-relative ref to the source. Everything
else gets a fully absolute ref to the source. To clarify:

Cells D510, E5:E10 are merged: DV formula in E5 is "=E5<>$D5"
Treat as single since they are the same size and relative location.

Cells D510, E5:E7 are merged; E8:E10 are single:
E5 gets same as above. E7:E10 gets "=E7<>$D$5"

Cells D510, E5:E10 not merged: DV formula in E5:E10 is "=E5<>$D5"

Cells D510 not merged, E5:E10 merged:
OOPS! I guess the only ref is "=E5<>$D5" and the others adjacent
cells in D are ignored OR all are ignored if E5:E10 are the source for
other cols further to the right.

The OP also suggests filtering be used to disallow specific text and so
the target cells formula will need to be substituted (respectively)
with the above to prevent entering the specified text:

"=AND($D5<>"STO",$D5<>"INF",$D5<>"BAC")"
OR
"=AND($D$5<>"STO",$D$5<>"INF",$D$5<>"BAC")"
This will absolutely prevent entering any of these in any cell with DV
regardless of upper/lower case. It will allow "STO ", which will appear
the same as "STO" but that's a whole other issue. The FIND() or
SEARCH() functions would have to be used depending on whether it needs
to be case sensitive (FIND) or if wildcard characters need to be used
(SEARCH). If both are needed then a defined name mega-formula is
probably the best solution, where a separate one for each respective
usage. I don't know if they could be wrapped in an IF function so the
DV formula includes either scenario, but I'm confident you would know
if that was possible.

I guess the question is whether or not the OP wants to handle entry
validation via worksheet events OR include it in the worksheet's design
via code (vs doing it manually). Since I always build this into my
worksheets at design time, I've never applied DV via code and so I'm
sure you'll come up with an AWEsome solution, ..as you usually do!

Garry

 

[Mik]

Rick Rothstein pretended :

I'm guessing that since you posted in a programming newsgroup, that a VB
solution would be acceptable. Before giving you code, however, can you
clarify a couple of things please? First, are merged cells in your columns
"paired"? That is, if D510 are merged, then is E5:E10 also merged, orcan
E5:E10 be composed of individual cells (or possibly cells merged in a
different pattern than those adjacent to it)? Second, are Columns D andE the
only columns that will have the relationship you described or are thereother
paired columns that must be considered?

Hi Rick,
It occured to me to redirect the OP to an Excel group due to him only
stating ref to "my worksheet...", and suspected he was not looking for
a VB[A} solution. I did not do that because I realized that if Data
Validation was needed 'after the fact' then code may be required to
apply that conditionally.

The OP implies that he has control over the design/structure of the
worksheet (vsv "my worksheet..."), and so IMO DV is the way to go. How
that's implemented will be conditional on if the source (containing the
criteria) OR target (restricted) cells are merged or not, and so is why
I gave 2 examples of the DV formula to use. In this case, the code we
suggest needs to evaluate both conditions to determine what formula to
apply. AFAICT, the determing factor for the target cells is whether the
source cells are merged or not, as to what DV should be applied based
on two things: their relative location to the merged source, and if
they are merged, single, or a mix of both.

Otherwise, I suspect that single source cells would be handled row by
row with a column-absolute, row-relative ref to the source. Everything
else gets a fully absolute ref to the source. To clarify:

  Cells D510, E5:E10 are merged: DV formula in E5 is "=E5<>$D5"
    Treat as single since they are the same size and relative location.

  Cells D510, E5:E7 are merged; E8:E10 are single:
    E5 gets same as above. E7:E10 gets "=E7<>$D$5"

  Cells D510, E5:E10 not merged: DV formula in E5:E10 is "=E5<>$D5"

  Cells D510 not merged, E5:E10 merged:
    OOPS! I guess the only ref is "=E5<>$D5" and the others adjacent
cells in D are ignored OR all are ignored if E5:E10 are the source for
other cols further to the right.

The OP also suggests filtering be used to disallow specific text and so
the target cells formula will need to be substituted (respectively)
with the above to prevent entering the specified text:

  "=AND($D5<>"STO",$D5<>"INF",$D5<>"BAC")"
  OR
  "=AND($D$5<>"STO",$D$5<>"INF",$D$5<>"BAC")"
This will absolutely prevent entering any of these in any cell with DV
regardless of upper/lower case. It will allow "STO ", which will appear
the same as "STO" but that's a whole other issue. The FIND() or
SEARCH() functions would have to be used depending on whether it needs
to be case sensitive (FIND) or if wildcard characters need to be used
(SEARCH). If both are needed then a defined name mega-formula is
probably the best solution, where a separate one for each respective
usage. I don't know if they could be wrapped in an IF function so the
DV formula includes either scenario, but I'm confident you would know
if that was possible.

I guess the question is whether or not the OP wants to handle entry
validation via worksheet events OR include it in the worksheet's design
via code (vs doing it manually). Since I always build this into my
worksheets at design time, I've never applied DV via code and so I'm
sure you'll come up with an AWEsome solution, ..as you usually do!

Garry

Thank you both for your input.

To confirm, I did intend to compare cells using VBA, but I will
welcome 'manual' Data Validation if this does the Job.

Answer to Rick....
Merged cells are not necessarily paired (i.e. D510 not necessarily
the same as E5:E10). It could be the same, but it could also comprise
of individual cells or cells merged with other adjacent cells in the
same column (different pattern).

Columns D and E are not the only columns to consider. The relationship
between columns is B:C, D:E, E:F, G:H etc...

The purpose is for product calendar, so need 365 days (columns) for
each operator, and there are TWO operators, so there will be 365
paired columns (730 total columns), where B:C need to be checked, D:E
need to be checked etc..

These columns only need to be checked at the time of data input, and
do not continually need to be checked / re-checked.

We need to prevent both operators ONE and TWO from having the same
adjacent cells (they can't do the same function at the same time).

However, as initially mentioned, i want to prevent only text 'INF',
'STO', and 'BAC' from being input. All other text input is OK.

Answer to Garry...
I will trial your Data Validation, and let you know how i get on.
My initial thoughts were to go with VBA because the amount of cells
that would require DV - it might get too complex.

However, I could be wrong.

Thanks again to both of you for your input.
Mik