Prevent user input, if adjacent cell text is identical
 

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 ("D5:D10") 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

Prevent user input, if adjacent cell text is identical
 

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 wrote:

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 ("D5:D10") 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

Prevent user input, if adjacent cell text is identical
 

On 9 May, 01:18, CB wrote:
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 wrote:
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 ("D5:D10") 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.

Prevent user input, if adjacent cell text is identical
 

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 ("D5:D10") 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. (DataValidation...)
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

Prevent user input, if adjacent cell text is identical
 

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 D5:D10 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?

--
Rick (MVP - Excel)



"Mik" wrote in message
...
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 ("D5:D10") 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

Prevent user input, if adjacent cell text is identical
 

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 D5:D10 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?

--
Rick (MVP - Excel)

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 [s]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 D5:D10, E5:E10 are merged: DV formula in E5 is "=E5<$D5"
Treat as single since they are the same size and relative location.

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

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

Cells D5:D10 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

Prevent user input, if adjacent cell text is identical
 

On 9 May, 23:23, GS wrote:
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 D5:D10 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?

--
Rick (MVP - Excel)

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 [s]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 D5:D10, E5:E10 are merged: DV formula in E5 is "=E5<$D5"
* * Treat as single since they are the same size and relative location.