If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
fill any random cell in a column and block all other cells
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. |
#2
|
|||
|
|||
fill any random cell in a column and block all other cells
--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 DataData ValidationAllow '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 --------------- Jacob Skaria "SK" wrote: 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. |
#3
|
|||
|
|||
fill any random cell in a column and block all other cells
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" wrote: --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 DataData ValidationAllow '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 --------------- Jacob Skaria "SK" wrote: 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. |
#4
|
|||
|
|||
fill any random cell in a column and block all other cells
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 --------------- Jacob Skaria "SK" wrote: 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" wrote: --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 DataData ValidationAllow '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 --------------- Jacob Skaria "SK" wrote: 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. |
#5
|
|||
|
|||
fill any random cell in a column and block all other cells
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" wrote: 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 --------------- Jacob Skaria "SK" wrote: 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" wrote: --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 DataData ValidationAllow '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 --------------- Jacob Skaria "SK" wrote: 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. |
#6
|
|||
|
|||
fill any random cell in a column and block all other cells
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 --------------- Jacob Skaria "SK" wrote: 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" wrote: 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 --------------- Jacob Skaria "SK" wrote: 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" wrote: --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 DataData ValidationAllow '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 --------------- Jacob Skaria "SK" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|