fill any random cell in a column and block all other cells

[SK]

Hi,
I am using the latest version of Microsoft excel (.xlsx).
In a column, I just want one cell to be filled (at any time) and rest as
locked; but that cell could vary/be different.
And I should also be able to delete that cell and then be able to write on
any other cell on the same column (only one cell should be filled at any time
and rest should automatically be blocked). And I want this for say 100 rows.
E.g.:
Column A is the ‘timing slot’; Row 1 is with the ‘staff name’; and column B
to ZZ are blank where I will be putting ‘yes’ in each column but for one cell
only and if I try to enter ‘yes’ in another cell in the same column – it
must not allow me to do so until I delete the earlier entered/completed cell
(ie until I don't delete the ‘yes’ in the other cell of this column).
Thank you very much in advance. Your help would be highly appreciated.

 

[Jacob Skaria]

--Select the range B2:Z100 (or for a test try B2:J10). The cell referenced in
the formula is the active cell in the selection..Active cell will have a
white border even after selection.,

--From menu Data>Data Validation>Allow 'Custom' In formula type
'to check for any text
=COUNTA(B$2:A$10)=1

'to check for the text 'yes' but allow other text
=COUNTIF(B$2:A$10,"yes")=1


If this post helps click Yes

 

[SK]

Hi, Thanks alot for your help and prompt response.
This stopped me inputting two (or more) cells in column B but then I can not
enter/fill any cells in column C at all.
Also, The other issue is that if I copy ‘yes’ from earlier cell to another
cell on the same column (e.g. column B or C) – then it doesnt restrict and I
can still fill multiple cells on the same column!
Your help would be appreciated. Thanks

 

[Jacob Skaria]

I missed to correct the formulas. after testing. It should have been B
instead of A.

With your selection B2:J10 and B2 as active cell; try

=COUNTA(B$2:B$10)=1

'to check for the text 'yes' but allow other text
=COUNTIF(B$2:B$10,"yes")=1


If this post helps click Yes

 

[SK]

Thank you very much – truly appreciated. You have already got a yes from me
with bundle of thanks.
Just a slight issue; if I copy ‘yes’ from earlier cell to another cell on
the same column then it doesnt restrict and I can still fill multiple cells
on the same column!
Also, what if I need to do the same exercise on Excel 2003
Thanks

 

[Jacob Skaria]

In Xl 2003 you can find this under menu Data >Validation

For a more robost solution you will have to use a macro...

If this post helps click Yes