I have a cell containing data validation list and what i want to do is that when i choose the 1st value from this list .. a number (for examole 1) appears in another blank cell .. and if i choose the 2nd value from the list .. another number (let's say 2) appears in the blank cell.
So how to use the if statement in a cell that contains a data validation ?!!
I have a simple work book. Please refer to the attachment Book1.xls.
In B1, the data is limited (by Data Validation with a drop down List) to Yes and No.
I need to limit the range of data in D1 as, If B1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if B1 = No then the data entered into D1 should be Less than or Equals to 50
I prefer to do it with Data Validation.
I am trying to create a data validation rule that only allows a neagtive value to be entered if the word "backcharge" appears in an adjacent cell. So far I have come up with = if(d2="backcharge",<=0, .... But I am not sure how to finish it off.
View 4 Replies View RelatedCan you use data validation as part of an IF statement?
Example - I want a cell in column B to check a cell in column A. If the cell in A has "No" entered, B will show "Yes". If there is anything else in A then the user can choose from a dropdown in column B.
I have a cell (A2) containing a data validation list and i want to make it blank if another cell (A1) is blank .. else if (A1) is not blank then view the list in (A2)Is that possible ?
View 3 Replies View RelatedI am having trouble with a dependent dv list. Please see below:
=INDIRECT(IF(I5="Support",Internal_Cost_Centres,Project_Codes))
where
Support is a named list from the parent dv list
Internal_Cost_Centres is a named list
Project_Code is a named list
When I select Support from the parent dv list, it will return the list named Internal_Cost_Centres, however, when I select something else it doesn't work.. there are 3 options in all on the parent list (Support, Production, Project) and whether Production or Project selected, I want it to return the same list - Project_Codes.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedI have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI have a validation list list in column E "Check, Charge, Credit Card, Other". in another column i have what i thought was a straight forward IF statement: =if(E3="Check",9999,"[ ]")
It does not recognize "Check" as the condition. Is there a tweak i can do to get it recognize the data validation.
I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.
I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.
[url]
To keep it simple, I'm only supporting pasting a single cell at a time.
Now my problem is this:
Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.
The following post just suggests disabling paste whenever data validation is present:
[url]
But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.
I need a simple IF statement that look up in Column C for any text, and then add the value from Combobox "txtfloors" to Column B .
View 1 Replies View RelatedI have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
i am going to be making other columns with data. i want it to highlight the cell from the new columns if the value is higher than the standard.
not quite sure how to do this? do i make an if statement and put it in the cell, then go and input my data over it?
I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07
can i do two data validation on the same cell
the first one to make a list form Define name "Lists"
and the other to make sure the name is not repeated
I have already set up a data validation, if if you pick a region, you only get that regions items in the next column.
A
B
C
D
E
[Code]...
But what I would like to do is in Column E(for this example) have 3 extra lists for Location in each region. So if I pick S in A, then in E I only want the Southern options.
I have checked out this website, but I cant seem to work out how to add the third dependence [URL]....
I am trying to set the data validation on a cell based on another cell value
So basically if the User enters say CAN in cell A2, cell B2 should use "listCAN" range as the data validation list
and if i enter US in A2, B2 should use "listUS" as the data validation list
How can I create a drop down list, where the items available for selection in this drop down list are contingent upon another selection made in a seperate drop down list.
View 2 Replies View RelatedHow can I check if a cell has data validation with a VBA procedure?
I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation.
I'm building some work life balance spreadsheets for employees to show satisfaction levels / hours etc...
For the satisfaction piece - users will enter a value between 0 and 5 with 5 = happy as larry and 0 = ready to quit. Next to this field is a comments box. This comments box only needs to be populated (from a data validation list) if the staff are unhappy.
I need the file to prevent users entering a value less than 3.99 without selecting a comment from the validation list. Users will go into the file and update for their own lines so the check can be written into the save function maybe???
I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.
In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.
I have attached a sample spreadsheet if I've not explained very clearly.
TIMESHEET sample.xlsx
Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell?
For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.
I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......
is it possible to find the number of row for selected value in cell with data validation?
for example:
sheet2
cell G13 have data validation format
Allow = List
Source = 'sheet1'!E:E
Ignore blank = yes
sheet1
columnn E contents file names with hyperlink
FileName1.txt
FileName2.txt
FileName3.pdf
.....
FileNameN.txt
I choose FileName3.pdf
I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
I need a hint for one complex validation. In column B I am having emails and need to validate that after ” @ “ the extension is yahoo.com. If something different from that I need the cell highlighted.
View 4 Replies View RelatedSituation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
I'm working on a few dynamic KPI charts and could use a little help with figuring out data valuation for the End Date value. In the attached workbook I am using data validation to pick a start date from a dynamic list (Dates) in E2:Exxx. Cell B2 has 2/8/2010 picked. I would like to have a drop down in B2 show the listing of all dates in E2:Exxx which occur after 2/8/2010, so the filtered list would be in the range E3:E49.
View 2 Replies View Related1- If I wish to write in the cell I can do it ?
2- When I do not wish to write in the cell I can do it (Its meaning protect the cell.)
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.
I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.
How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......