| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I stop entry in one cell if another cell is occupied??
Hi,
I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Custom Data validation with formulae:
Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Thanks, that works well for stopping input into the cell but it gets rid of
my list validation is there any way to do both, or another way around it. "Stefi" wrote: Custom Data validation with formulae: Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
=AND(ISBLANK(B1),yourformula)
Regards, Stefi Gazz_85 ezt *rta: Thanks, that works well for stopping input into the cell but it gets rid of my list validation is there any way to do both, or another way around it. "Stefi" wrote: Custom Data validation with formulae: Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Thanks, ive tried it and cant get it to work. my list is situation in the
following fields BF3:BF7 So this is my formula as you suggested. however same thing happens it either removes my list, are gives me an error. any more suggestions??? =AND(ISBLANK(D3),$BF$3:$BF$7) "Stefi" wrote: =AND(ISBLANK(B1),yourformula) Regards, Stefi Gazz_85 ezt *rta: Thanks, that works well for stopping input into the cell but it gets rid of my list validation is there any way to do both, or another way around it. "Stefi" wrote: Custom Data validation with formulae: Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Sorry, I didn't notice that you already have a validation list, you can't
have two validations of different type. You have to use a Worksheet_Change event like this to make a workaround: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <= 2 Then 'A:B columns othercol = IIf(Target.Column = 1, 2, 1) If Not IsEmpty(Cells(Target.Row, othercol)) Then MsgBox "Other column is not empty!", vbOKOnly, "Invalid input!" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub NB! It works only with columns A and B, adjust it to your live columns! Post if you need help to install it! Regards, Stefi Gazz_85 ezt *rta: Thanks, ive tried it and cant get it to work. my list is situation in the following fields BF3:BF7 So this is my formula as you suggested. however same thing happens it either removes my list, are gives me an error. any more suggestions??? =AND(ISBLANK(D3),$BF$3:$BF$7) "Stefi" wrote: =AND(ISBLANK(B1),yourformula) Regards, Stefi Gazz_85 ezt *rta: Thanks, that works well for stopping input into the cell but it gets rid of my list validation is there any way to do both, or another way around it. "Stefi" wrote: Custom Data validation with formulae: Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Thanks,
Works well to stop input, but removes my list validation. is there anyway to do both, or another way around it. "Stefi" wrote: Custom Data validation with formulae: Select column A: =ISBLANK(B1) Select column B: =ISBLANK(A1) Regards, Stefi Gazz_85 ezt *rta: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I stop entry in one cell if another cell is occupied??
Thanks,
Works well to stop input, but removes my list validation. is there anyway to do both, or another way around it. "Gazz_85" wrote: Hi, I have a spreadsheet that is being used to updated and display jobs for production. I have two colums one for short term jobs and one for long term jobs. The value for each cell is validated and only anything from a drop down list can be selected. So what I need to do now is make sure if a value is selected for short term, nothing can be entered in the long term colum...as a job can only be either long or short term not both. Any help???? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| subtract from a cell if another cell is occupied | Excel Discussion (Misc queries) | |||
| Selecting occupied cell over empty ones | Excel Discussion (Misc queries) | |||
| Selecting occupied cell over empty ones | Excel Discussion (Misc queries) | |||
| subtract from numeric cell when alpha cell is occupied | Excel Worksheet Functions | |||
| stop cell entry being copied to other cell | Excel Worksheet Functions | |||
Hybrid Mode
