I 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 RepliesI'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 currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
I am working on a resource management type workbook. In the first sheet,(Project Assignment) managers can enter staff, staff type and hours needed for the next three weeks. In a separate sheet,(Total Hrs per week) I have formulas set to total the hours entered from the first sheet for each resource - using the formula =SUMPRODUCT(--(staffassignments=$B4),--(Week1)) for each week, for each resource. That woks fine.
What I would like to do, if possible, is to present a type of data validation, dialog or popup when the resource's "total hrs per week" total = 40 hours. Different managers use the same resources, so one person may have time entered in multiple times for different projects in the Project Assignment sheet.
Is it possible to use the totaling formula (=SUMPRODUCT(--(staffassignments=$B4),--(Week1))) and an if statement in the data validation or conditional formatting to let managers know that the resource is fully utilized?
Should be simple but I can't get this to work,
I have used =value(a1) that prevents any formula, but it also prevents a zero being entered.
I have amended trying and/or statements in the validation but then it allows zeros and formulas!
I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error.
View 9 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
What I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")
I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.
My Table has a data validation list in one row of a table. And different formulas in different rows.
The Table is expanded only into columns. Which means, there will be no new rows...only columns will be added.
If we enter text into the header cell in a new column, the whole new column gets formatted, but the list and formulas do not auto fill into new columns.
Of course an easy way is to copy and paste a column...but is there a way to auto fill lists and formulas into columns of the table? Similar to how the rows get autofilled?
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
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.
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 RelatedI 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 ?!!
How 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)?......
I'm looking for a formula which will enable typing (in cell A1) positive numbers between 0-1000000 but will prevent the user from leaving that cell in an empty state. Empty cell means: cell with space(s) or deleting the legitimate value that is, already, in it).
I must emphasize that I know how to handle this by VBA code and/or by using a "named" pre-defined list of values + un-checking the "ignore empty cell" option - but I would like to know, once and for all, the solution WITHOUT using Macro and/or 'List' (if any..)
I tried that, without success:
=AND(A1>=0,A1<=100000,NOT(ISBLANK(A1))
I set up a spread*** with drop-down menus for some of the cells. The user can only select values from that list and an error message pops up when something is typed it that is not in the list (via Data Validation
Error Alert).
So this works all fine ... except if the user copies and pastes a cell with a different drop-down menu into a cell overwriting the existing validation.
I cannot totally lock the work*** and if I lock the cells with the drop-down menus nothing can be changed. So how can I prevent this from happening? Is there a way to block all copy/paste operations that are copying the validation? I know that with the Paste Special I can have cells copied without the validation.