Cell Validation: Conditional Blank Value Prohibited
Aug 10, 2006
I would like to validate data but cant quite get the formula right. I have a cell (say F7) that cannot be blank if (say E7) another cell equals a text value (say "S"). Anyone have an idea? I am stumped at this seemingly easy task. I am applying the validation below to F7.
I forgot to add the valid combinations:
E7 = P and F7 = Entry: OK
E7 = P and F7 = Blank: OK
E7 = S and F7 = Entry: OK
E7 = S and F7 = Blank: INVALID
I have tried the formula: =IF(AND(E7="S",ISBLANK(F7)),1,0) within the validation window but blank entries are still allowed. I put this code in adjacent cells as a formula, the values are correct either 1 or 0.
View 4 Replies
ADVERTISEMENT
Aug 26, 2009
Would like to know how to place validation on cell a2 to NOT EQUAL BLANK.
View 11 Replies
View Related
Sep 11, 2013
I have a tracking sheet... in that I got in column C say "priority" there I have a drop down list date say P1, P2, P3
Now I need to restrict that if a user enters a data in the C2 and trying to enter a data in D2 leaving the drop down list data blank in C2. A error message saying "pls select priority forst" etc...
I got the following solution from moderator:
You can apply the Custom Data Validation formula =LEN(C2)>0 to D2, making sure that Ignore Blank is unchecked.
It works but, when I enter something in that cell it gives the error message and when I click OK or Cancel it just ignore the conditions and leave the data what ever I entered and moves further.
View 1 Replies
View Related
Jul 13, 2013
I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.
View 3 Replies
View Related
Dec 12, 2012
I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.
View 6 Replies
View Related
May 16, 2008
Excel 2003 Scenario:
Column H contains text data as follows down the rows: BIKE/CAR/TRUCK.
Column N contains text data that would be entered free text by user down the rows; however, if the data entered in column N is not contained in column H it would warn the data is not contained in column H. Example: BOAT is not found in column H. Note; it could contain all text in column H for that row or just one word in column H for that row.
The goal is to recognize the text entered in column N is not contained in column H, warn and change the color of the text in column N to red.
I attempted to use Data/Validation, and Conditional Formatting; none seem to have a contain function.
Is there a formula or VBA I may use or any thoughts?
View 9 Replies
View Related
Jan 6, 2014
I need a hand with conditional formatting if possible?
In the attached document i need the cell fill for the cells containing values (in column C) to change if data on sheet 2 is anything other than blank.
e.g. Sheet 1 C3 would be green fill and C4 would be no fill as Sheet 2 B3 contains a date and B4 does not
View 4 Replies
View Related
Jan 22, 2009
I need to automate a conditional fill down in excel. I have a sheet, some of the columns have rows of empty cells (the amount of empty cells varies). Whenever there is an empty cell I need to fill down, however when a new value is encountered that must then be filled down and so on. This bit I have as:
Set rngWBSv1 = Range("c2", Range("c65536").End(xlUp))
For Each clWBSv1 In rngWBSv1
If clWBSv1.Value = "" Then clWBSv1.Value = clWBSv1.Offset(-1, 0).Value
Next clWBSv1
Set rngWBS2 = Range("d2", Range("d65536").End(xlUp))
For Each clWBS2 In rngWBS2
If clWBS2.Value = "" Then clWBS2.Value = clWBS2.Offset(-1, 0).Value
Next clWBS2 ......................
View 4 Replies
View Related
May 15, 2008
I have a totals spreadsheet that links many other spreadsheets. I need to set up a formula for conditional formatting if the cell is blank (NOT ZERO). I need to ensure that the users are inputting zeros and not leaving the cells blank. I tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)<"""
View 3 Replies
View Related
Apr 1, 2014
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
View 3 Replies
View Related
Feb 4, 2010
I've read dozens of threads on how to have conditional formatting ignore blank cells but I am not understanding how to make it happen myself...
Logic: Highlight the cells that are >=10 but ignore the blank cells that have functions in them
View 3 Replies
View Related
May 22, 2013
I need to use conditional formatting to recognize blank cells meaning totally blank and not cells with formulas returning 0 what i must use to get this result?
View 9 Replies
View Related
Nov 4, 2012
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
View 4 Replies
View Related
Mar 6, 2014
I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?
View 1 Replies
View Related
Oct 31, 2008
As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)
If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.
If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.
It works, but doesn't work.
If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!
I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.
View 4 Replies
View Related
Nov 3, 2009
I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.
View 3 Replies
View Related
Nov 30, 2008
I'm trying to apply conditional formatting (shading) to cells that are left blank.
(Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!)
(When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)
View 2 Replies
View Related
May 22, 2012
I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried
=VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment).
I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.
View 3 Replies
View Related
Sep 26, 2009
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
View 2 Replies
View Related
Nov 14, 2013
I would like to highlight (conditional format) the "response required by" (Row A) cells / dates if the "current date" (cell $B$2) is exceeded AND if the "response provided By" (Row B) remains BLANK:
Current Date
=Today()
Response Required By
Response Provided By
29-10-2013
23-10-2013
[Code] .......
View 6 Replies
View Related
Dec 30, 2007
I have a Sheet 1 which derives some values from Sheet 2.
I want to color cells in Sheet 1 which do not have input from Sheet 2, but the formulas in the Sheet 1 cells are blocking ISBLANK conditional formatting.
Is there a way around this?
View 9 Replies
View Related
Feb 2, 2008
i have two sheets in workbook named as shtbiz,shtbiz1.
so column A of my shtbiz1 sheet i have 100 datas.the by using following code i can fill the datas of shtbiz1 in column A of shtbiz.
Dim strValItems
strValItems = ""
Set rngValList = ShtBiz1.Range("A6", Range("A10000").End(xlUp))
With ShtBiz.Range("A6:A10000")
. Validation.Delete
For Each rngValCell In rngValList
If Len(strValItems) > 0 Then strValItems = strValItems & ","
strValItems = strValItems & rngValCell.Value
Next
.Validation.Add xlValidateList, , , strValItems
End With
so my problem is i need to add blank value in the top of the list .ie first value should be blank then other values which read from shtbiz1 has to be followed.
View 6 Replies
View Related
Apr 10, 2004
Sheet 1 has a LIST of hockey player
Sheet 2 has my friend's team (12 players). Each, a dropdown list on the player of sheet 1.
When a player will be eleminated, i will manually change his background to red in the list of sheet 1.
I want the background of the sheet 2 related to the player eliminated to change to red as well at the same time... how to do it automatically, without having to put a conditionnal formatting on EACH cell in sheet 2 ?
View 6 Replies
View Related
Mar 17, 2006
I have a spreadsheet with some cells setup with a drop-down list containing Y, N or N/A
This is being used on a TabletPC but if I make a mistake or need to change back to a blank field I have to invoke the soft keyboard, activate the cell and hit backspace then close the soft keyboard - quite a long-winded procedure just to change an incorrect choice!
What I would like to do is add a blank to the list so if I have to revert back to a blank I can just use the stylus to choose a blank from the chooser list.
How do I add the option of inputting a blank from the Data Validation List bearing in mind I am using the Data Validation Source box for entering my choices directly and not specifying a range of cells?
I have tried adding "" and even a space to no avail.
View 11 Replies
View Related
Aug 21, 2009
I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?
Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.
View 10 Replies
View Related
Mar 4, 2003
I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
View 9 Replies
View Related
Jan 14, 2010
I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.
Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
Also I'm using Excel 2007
View 14 Replies
View Related
May 21, 2009
I have conditional formatting for a range of cells eg. =COUNTA(AC3,AD3,AE3,AH3)
It is currently set so that cell A3 changes to Green if AC3 is populated, Red if AD is populated, Orange if AE3 populated & white if AH is populated.
AE3 has data validation (Yes or No).
I need AE3 to change to White if no is selected and Orange if Yes is selected.
View 9 Replies
View Related
Jul 19, 2006
Conditional formatting: If a cell contains a string, say "NCT" or "nct", conditional format shall work. The string may be with a date entered i.e. "05/05/2006 NCT"
Same said cell is currently set to only allow dates. How can i achieve that Data Validation: Can data validation be set to only allow a date, and/or "date NCT" as above?
View 5 Replies
View Related
Jan 16, 2007
I have been trying to figure this out for days and have searched on all the postings and cannot find my answer. I am working off of two worksheets. Sheet #1 has 3 columns. Column A "Type of Bar", Column B "Type of Activation", Column C "Amount Spent". Column A has 2 drop down boxes, Pub & Nightclub. Column B has 2 drop down boxes, Sampling & Training. Sheet #2 is is a tracker that is going to add up certain criteria off of Sheet 1. For example, I need to get the sum of All Pubs and Sampling. I also need to track All Pubs and Training, Nightclub and Sampling and Nightclub and Training. I have tried the SUMIF, IF, and Conditional Sum. I keep running into a RANGE error.
View 2 Replies
View Related
