Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

restrict cell entry cellb

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Restrict cell entry in 'Cell-B' based on value entered in 'Cell-

Restrict cell entry in 'Cell-B' based on value entered in 'Cell-

ResolvedVersion 2003

Francis has attended:
Excel Advanced course

Restrict cell entry in 'Cell-B' based on value entered in 'Cell-

Hi

How do I do the following:

1. Upon entering '0' in Cell-A, I would like Cell-B to be blocked (inaccessible) from modification with an error message saying 'Not Granted!'

2. Upon entering '1' or '3' in Cell-A, I would like Cell-B to be open for value entry

Thanks a lot!

RE: Restrict cell entry in 'Cell-B' based on value entered in 'C

Hi Francis. You need to use the Data-Validation tool to do this, although that may limit you to one criteria of data entry. In cell B1 select Data-Validation, Allow: "Custom" and enter "=RC[-1]=1" in the formula dropdown. This will permit editing of cell B1 unless there is a 0 in cell A1.

Two criteria for data entry into a cell may be possible if you tease them apart and send a separate value to another cell and run the data validation tool on that, but really a simple macro might be the best way forward.

Hope this helps,

Anthony

RE: Restrict cell entry in 'Cell-B' based on value entered in 'C

hhmmm.....the formula is actually rejecting all entries to Cell-A

NB: Cell-A is actually 'D7' and Cell-B is actually 'G7'

RE: Restrict cell entry in 'Cell-B' based on value entered in 'C

Hi Francis. My formula includes a relative reference which you'll need to modify if the cells aren't adjacent. For this sort of criteria selection have you tried a macro?

Anthony

Mon 11 May 2009: Automatically marked as resolved.

Excel tip:

Freeze Rows and Columns to keep lables displayed

You can freeze rows and columns in your worksheet so they don't move.

This allows you to keep row and column labels displayed on your screen as you move through a large worksheet.

Click below and/or to the right of the cell(s) you want to freeze. (NB. Excel freezes ALL the rows above and ALL the columns to left of the selected cell)

Click on the 'Windows' menu and selct 'Freeze Panes'.

Lines appear in your worksheet. The required rows and columns are frozen and remain on your screen as you move through your worksheet.

To unfreeze rows and columns, click on 'Window' menu and select 'Unfreeze Panes'.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.14 secs.