How to lock a row in a spreadsheet based on the value in a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Prasad - 15 Dec 2005 22:01 GMT

Hi,
I am creating an personal expense datasheet. I would like to lock the entire
row if I click/ select the cell. Like I entered my expenses for that day in
cell A1 to A6. And I want that entire row to be locked if I put some value in
A7.
i.e. lets say I put a value 'Y' in cell 'A7'
I want the entire row to be locked if the A7 cell has a value Y and if the
A7cell is blank that row should be editable.

Hope, I am able to put my question correctly. Would appreciate a correct
response on it.

Thanks in advance,
Prasad

Reply to this Message

Debra Dalgleish - 16 Dec 2005 05:32 GMT

You could use Data Validation to prevent typing in the cells if there's
a Y in column G of the row. For example, select cells A1:F20

Choose Data>Validation
From the Allow dropdown, choose Custom
In the Formula box, enter: =$G1<>"Y"
Click OK

> Hi,
> I am creating an personal expense datasheet. I would like to lock the entire
[quoted text clipped - 10 lines]
> Thanks in advance,
> Prasad

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

Prasad - 16 Dec 2005 13:20 GMT

Hi debra,
Thanks for your reply. I tried it. But, it is not working as expected.
When I use the formula and try to edit it is not allowing me to do..That's
fine..
But what actually happening is irrespective of a value in the G column, it
is not allowing me to edit any of the values in the range I selected.

what I wanted was if there is a value 'Y' in G column then only the range
should not be editable otherwise it should be editable.

Would highly appreciate help on it.

Thank you,
Prasad

> You could use Data Validation to prevent typing in the cells if there's
> a Y in column G of the row. For example, select cells A1:F20
[quoted text clipped - 18 lines]
> > Thanks in advance,
> > Prasad

Reply to this Message

Debra Dalgleish - 16 Dec 2005 17:18 GMT

What cells did you select, and which cell is the active cell (its name
is visible in the name box, at the left of the formula bar)?
What is the exact formula that you used?

> Hi debra,
> Thanks for your reply. I tried it. But, it is not working as expected.
[quoted text clipped - 33 lines]
>>>Thanks in advance,
>>>Prasad

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message