Solved Excel - Data Validation

October 19, 2011 at 09:05:53
Specs: Windows 7
Hi all, I'm using Microsoft Excel 2010

I need to limit the number of a cell, so I tried using Data Validation.

The cell that needs to have a limit is A1 - say the limit is, 50.
A1 contains a =counta(B1:B100) formula.

Is it possible for Excel to lock actions when the range of cells (B1:B10 reaches and passes the limit of 50?


See More: Excel - Data Validation

Report •


#1
October 19, 2011 at 11:13:20
I'm not quite what you mean by "lock actions when the range of cells reaches and passes the limit of 50.

If you are asking that the value in A1 never show anything greater than 50, then try this...

=IF(COUNTA(B1:B100)>50, 50, COUNTA(B1:B100))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 19, 2011 at 11:45:06
Actually, I want to make it impossible for me to write in more cells when there alreaday are 50 that are written.

thanks!!


Report •

#3
October 19, 2011 at 18:36:53
✔ Best Answer
Select B1:B100

Choose Data...Validation...Custom

=COUNTA($B$1:$B$100)<51

This will only allow data to be entered in B1:B100 when less than 50 cells contain data. Once the 51st entry is attempted, the formula will return FALSE and prevent the data from be entered.

If I were doing this, I'd change the message so that it said something like "Too Many Entries In Range."

That's a lot less confusing that the standard "A user has restricted values that can be entered into this cell.'

That message is "cell specific" when you asking the validation to be range specific.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question