Hi all,

I have input area on my sheet as following :

Item1
Item2
Item3
Item4

each of them sittin on one cell, I would like users to input from item1 to
item4 and I did it by using formula, but after input users can also erase
item1 and make a blank cell about item2. How to prevent it?

Clara

--
thank you so much for your help

Limitations to user entry are done by locking a cell under
Format=Cell=Protection tab (locked is the default value)

Then protecting the worksheet. (the locked setting is not enforced until
the sheet is protected). This, of course, imposes many limitations.

If the user is only to be excluded after making an entry, then you would use
the change event to unprotect the sheet, lock the cells, then reprotect the
sheet.

--
Regards,
Tom Ogilvy


"clara" wrote:

Hi all,

I have input area on my sheet as following :

Item1
Item2
Item3
Item4

each of them sittin on one cell, I would like users to input from item1 to
item4 and I did it by using formula, but after input users can also erase
item1 and make a blank cell about item2. How to prevent it?

Clara

--
thank you so much for your help

Hi Tom,

My data validation is to force user input data in continuous way from item1
to item4, so I use Custom and my formula is =trim(a1)<"", but even it is
calculated to False , there is no error prompt message box.

Clara
--
thank you so much for your help


"Tom Ogilvy" wrote:

Limitations to user entry are done by locking a cell under
Format=Cell=Protection tab (locked is the default value)

Then protecting the worksheet. (the locked setting is not enforced until
the sheet is protected). This, of course, imposes many limitations.

If the user is only to be excluded after making an entry, then you would use
the change event to unprotect the sheet, lock the cells, then reprotect the
sheet.

--
Regards,
Tom Ogilvy


"clara" wrote:

Hi all,

I have input area on my sheet as following :

Item1
Item2
Item3
Item4

each of them sittin on one cell, I would like users to input from item1 to
item4 and I did it by using formula, but after input users can also erase
item1 and make a blank cell about item2. How to prevent it?

Clara

--
thank you so much for your help

Hi Tom.

My input range is A1:A10, I use formula "=trim(a1)<"" as data validation

A1
A2 =trim(a1)<""
A3 =trim(a2)<""
A4 =trim(a3)<""
A5 =trim(a4)<""
....

A10 =trim(a9)<""

it doesn't work
--
thank you so much for your help


"Tom Ogilvy" wrote:

Limitations to user entry are done by locking a cell under
Format=Cell=Protection tab (locked is the default value)

Then protecting the worksheet. (the locked setting is not enforced until
the sheet is protected). This, of course, imposes many limitations.

If the user is only to be excluded after making an entry, then you would use
the change event to unprotect the sheet, lock the cells, then reprotect the
sheet.

--
Regards,
Tom Ogilvy


"clara" wrote:

Hi all,

I have input area on my sheet as following :

Item1
Item2
Item3
Item4

each of them sittin on one cell, I would like users to input from item1 to
item4 and I did it by using formula, but after input users can also erase
item1 and make a blank cell about item2. How to prevent it?

Clara

--
thank you so much for your help

I guess I misunderstood your intentions.

No, data validation does not force the user to make an entry. It can only
restrict what the user enters in the cell. You data validation approach is
meaningless for what you are trying to do.

--
Regards,
Tom Ogilvy


"clara" wrote:

Hi Tom.

My input range is A1:A10, I use formula "=trim(a1)<"" as data validation

A1
A2 =trim(a1)<""
A3 =trim(a2)<""
A4 =trim(a3)<""
A5 =trim(a4)<""
...

A10 =trim(a9)<""

it doesn't work
--
thank you so much for your help


"Tom Ogilvy" wrote:

Limitations to user entry are done by locking a cell under
Format=Cell=Protection tab (locked is the default value)

Then protecting the worksheet. (the locked setting is not enforced until
the sheet is protected). This, of course, imposes many limitations.

If the user is only to be excluded after making an entry, then you would use
the change event to unprotect the sheet, lock the cells, then reprotect the
sheet.

--
Regards,
Tom Ogilvy


"clara" wrote:

Hi all,

I have input area on my sheet as following :

Item1
Item2
Item3
Item4

each of them sittin on one cell, I would like users to input from item1 to
item4 and I did it by using formula, but after input users can also erase
item1 and make a blank cell about item2. How to prevent it?

Clara

--
thank you so much for your help