Questions tagged [data-validation]
The data-validation tag has no usage guidance.
169
questions
0
votes
0answers
17 views
Varying Chart Range Based on Drop Down Lists
I'm hoping to get some help with automatically updating the chart range in Excel based on what's selected in a drop down list.
I have 3 selections for my drop down list: All, Alberta and Saskatchewan....
0
votes
0answers
11 views
Filter data validation options by typing in google sheets
So I have in google sheets, a drop down list populated using data validation (created via script).
In the drop down cell when you start typing, it will filter the available
drop down options based ...
0
votes
2answers
55 views
Two Different Dependent Drop Down Lists on Excel with Same Name Range
I'm hoping to achieve the following (using lists from data validation).
A1 cell dropdown menu: Paint, Replace, or Repair
- A2 cell dropdown menu: Replace, Repair
- A3 cell dropdown menu: Paint, ...
0
votes
0answers
11 views
Data Validation - Using truth table to define list options
I have a worksheet where I would like two drop down lists, one for screw size, and one for screw length. Only certain lengths are available for each size, so I want the length drop down to read the ...
0
votes
0answers
14 views
Excel - Force rows to follow pattern
I have an excel sheet of data which I am trying to sort so it follows a pattern (in order to automate the filling of a second sheet). The first sheet in question looks like this:
Example
So the ...
0
votes
0answers
12 views
How to autopopulate cells by changing the year of a car loan payment
I've been attempting to autopopulate the cells of a table (not yet formatted as a table), whose cells are configured to change values, when altering the years or interest of a car loan. However, my ...
1
vote
1answer
45 views
Pivot Table ineffectively capturing Multiple Selection Data Validated Cells
I have created a tracker which has several columns that allow multiple selection of values via data validation.
The goal is to create dashboards with slicers to analyse this data. For eg- Lets assume ...
0
votes
1answer
11 views
Optimize code to check a Data Validation cell to pick another cell
I have cell E2 that contains a Data Validation group: "online", "article", "journal", "proceedings" and "magazine". I am populating cell K2 with a formula that is based on E2's entry. If I select an ...
0
votes
0answers
720 views
How To Apply Multiple Data Validation In One Cell In Excel Worksheet
I want to apply 2 conditions on a range of cells.
The first one is each cell takes value from a drop down list. I implemented this by Data Validation dialog box, selecting List option and typing the ...
0
votes
1answer
94 views
Using data validation to list a column
I have a workbook with a table that has headers with names and group. The names column is distinct (no name is repeated) whilst the group column has values (like G1, G2, G3 etc) which are repeated.
I ...
1
vote
2answers
96 views
Data Validation in Excel - Two Cells can't be the same
I have two columns with headers "Sending" & "Receiving" and I'm working on adding data validation layers to the spreadsheet.
The cells already also have a data validation setting allowing only a ...
0
votes
1answer
36 views
Excel 2010 is repeating the optioned text in a data-validated cell. Is this a bug?
I have a table column that has data validation applied. It was working fine for a while, but now all of the entries are repeated – as in, if the option selected from the dropdown is "Per Diem," Excel ...
0
votes
1answer
121 views
What are the limitations of the “Data Validation” custom formula in Excel 2010?
TL;WR Why does =if(column()=16, list1, list2) give an error when being used as a List-type Data Validation function?
The long version:
I have been hollering at my computer all afternoon because I ...
0
votes
0answers
38 views
Data Validation Error checking in excel and how to highlight them further to flag the full row
I want to be able to conditionally format a full row that contains the flagged box.
Within excel 2016 I have setup columns with data validation arguments. Whenever an error is detected the cell is ...
0
votes
1answer
58 views
Data validation in Excel based on value matrix
I'm really struggeling to solve the following task. I have a data matrix where vehicles and features are matched.
Now I want to use this as source for Excels input validation feature in a second ...
0
votes
1answer
24 views
Ordering Excel data for data validation (dependant dropdown lists)
I am having a little bit of problem with making dependant dropdown lists.
I want to Select the name of my Adventurer and then Select a skill. The skill should be valid and usable for that class.
...
0
votes
1answer
116 views
How can I Protect/Lock a spreadsheet but allow the use to make Validation List Selections?
I would like the user to be able to select the Validation List drop-down and make a selection but if the cell is Formatted as "Locked" and "Hidden" and Protection is turned on, as soon as the ...
0
votes
0answers
40 views
Excel data validation
I need to validate data in Excel before loading into a MySQL table. (The validating data also available in MySQL tables.)
For example, a value for country is “India”. Before it inserting into MySQL ...
16
votes
4answers
5k views
Which characters are invalid for an MS-DOS filename?
I'm writing a filename I/O procedure in x86-16 assembly language. It takes eight characters (I don't need to support long filenames) from the keyboard and prints them to an on-screen text input field.
...
0
votes
3answers
674 views
How to prevent deletion or change of referenced values in Excel?
I have an Excel sheet which contains unique IDs, and another sheet which contains entries which reference by value (via Data Validation - List) those IDs from the first sheet.
I have enabled Data ...
0
votes
0answers
17 views
MS Access: Validate field based on another record
I have a table of records. Each record consists of a Machine/line and several productions flows related to the line. I am looking for a way to ensure that there can be only one "Active" record for ...
1
vote
1answer
72 views
Conditional defalt on data validation list
EDIT No. 2
*Ok, my previous articulations of my problem were, as pointed out, pretty rubbish. This edit, rephrases the question in a more straightforward manner, summarises the context and provides ...
0
votes
1answer
81 views
Preventing the entry of formulas in cells
I'm using MS Excel 2007. I would like to restrict the data entry of some cells in a worksheet to certain numeric values and also forbid the entry of formulas in those cells.
As an example, given ...
-1
votes
1answer
44 views
Excel data validation rule involving two columns
The rule is "You can enter a number in this column or in the column next to it, but both columns cannot have a value (on the same row) unless one of them is zero.
These are all valid:
A.....B.
0........
0
votes
2answers
72 views
Dependent data validation rule in excel
I have a cell that has two dropdown choices Variable or Fixed. I am trying to allow the next cell over to all the user to enter any number when Variable is chosen but only allow users to enter 1 when ...
0
votes
1answer
1k views
Excel 2013-Force Date Format in a Cell
So, I recently ran into an issue with a customer where a column (END DATE) was formatted to be a date using the (Format Cells, Date, Type "DD-MMM-YY"), but when a user was typing in a date, they ...
0
votes
1answer
40 views
Using the data validation function
Is there a way when using the data validation function on a list that includes blanks to have the blanks not shown in the drop down menu??
1
vote
1answer
1k views
Excel Dynamic Drop Down Lists based on a Matrix
I am looking to control the options available in a series of drop down list (created using data validation) based on a control matrix.
In the example screenshot below, the first drop down list allows ...
0
votes
0answers
534 views
How to do Excel data validation on sum of multiple cells including empty cells
I need to validate the sum of several cells in a row at Excel.
For example:
I have cells F3 to R3.
I want to check if the sum from F3 to R3 is equal to 1.
F3 to R3 data:
I have used the formula ...
0
votes
2answers
218 views
Excel: Validate a decimal value in a cell making sure it contains one of four conditions
I have a sheet that people enter to track effort time. They need to enter in 15 minute increments therefor, the cell can only end in:
.00 or .25 or .50 or .75 as an acceptable entry. The number ...
1
vote
1answer
890 views
Using an Array in Excel's Data Validation
Problem Statement:
I am attempting to use data validation to set the criteria of input values within my range of $C2:$C19. I am using the Allow:List option of Data validation.
Attempt:
My attempt for ...
2
votes
1answer
1k views
Auto-populate cells in Excel
As you can see in my screenshot,
I have a table called "Payment Details Form" that needs to be filled and
printed for each individual of 100 employees:
In another worksheet, I have a data table of ...
2
votes
1answer
644 views
Excel allowing deletion of cell contents of data validated cell
I have a worksheet where only cells which should be edited are unlocked, and the whole worksheet is then protected. One of the cells where editing is permitted has data validation set, based on a 2 ...
0
votes
1answer
272 views
I want to write a code in vba with would split the arrays in a cell to multiple rows [closed]
I am trying to split data from a single cell to multiple rows as shown below.
I am using the below mentioned code:
Sub TransposeRange()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
...
4
votes
0answers
675 views
Check for damaged file mp3, m4a (aac) in Linux
I have around 15,000 music files stored on Ubuntu server (16.04), around 50% FLAC, 25% each mp3 and m4a (aac).
I think maybe 3-5% are corrupted due to HDD hardware failure. The problems accumulated ...
2
votes
1answer
65 views
Excel: Data validation list enable option for all values
I am using a data validation list to dictate the outcome of a SUMIF formula. I'd like the user to be able to select a specific geographical location in the drop down list or to return the sum for all ...
1
vote
0answers
80 views
How do I show Data Validation red circles when sheet is protected?
I'd like to show all invalid data on my worksheet using Data -> Data Validation -> Circle Invalid Data, but this is disabled when the sheet is protected (i.e. Review -> Protect Sheet). Is there a ...
0
votes
1answer
410 views
Multiple Data Validation Rules In Excel
I am trying to write a formula in Excel data validation that meets one or the other condition.
I tried the following, but it does not trigger the validation pop-up box.
The formula is as follows:
=...
1
vote
1answer
1k views
Data Validation From 2 Lists EXCEL 2010
I have two sets of dynamic lists, List1 & List2
Lists are created using OFFSET and COUNTA, so new entries are contained within the list etc.
In Cell A1 i want to use the data validation list ...
1
vote
2answers
941 views
excel data validation list only upper case
I want to do some data validation in my excel workbook. I want to use as many centralized values as possible for the data validation as the same values are used for multiple sheets. Now I have added ...
1
vote
1answer
81 views
Excel 2013 Formula to validate binary data and exclude blank cells (would also love help simplifying current formula)
I have people inputting data for 16 questions marking respondents' Yeses with 1 and Nos with 0, but in the past I have had trouble with silly errors either with blanks or with other numbers, so I am ...
0
votes
1answer
581 views
Data validation to allow users to enter a date (between ranges) OR N/A only
I would like to set data validation on a column, (say L), so that users can only input a date (between ranges) or N/A. I don't want any other entries to be allowed. I can do one or the other, but ...
-1
votes
1answer
722 views
Hide and unhide rows dependent upon pull down list
I have a Cell (D3) with a pull down menu of 8 items (all words). Dependent upon the pull down item selected, I want to hide, all other rows without that specific word in column K (11 columns over from ...
1
vote
0answers
444 views
Remove Duplicates when column data is in Drop Down List
I would like to create a dropdown list and make it remove duplicates from the table column where it draws the data from
Given a Table in sheet1 as shown in the figure below.
In Sheet2 I need a ...
0
votes
1answer
344 views
how to allow only specific format with data validation (hh:mm - hh:mm - shift scheduling)
so basically I need a formula which allows only data in the following format:
hh:mm - hh:mm (this is for shift scheduling, so e.g. 08:00 - 16:00)
Space before and after dash (-) is absolutely a ...
0
votes
1answer
2k views
Excel auto create next line with data validation
I want an Excel spreadsheet to automatically extend the range when a user enters data in the next row.
Simple example:
| A B C D
--+-----------------------
1 | 0 1 =A1+B1 =B1+C1
2 | ...
1
vote
2answers
22 views
Forcing negative input depending on type
I have a simple spreadsheet with three columns:
A Category (Data validation drop down list allowing only "Asset" or "Debt")
B Description (free form; e.g. Home (Asset), Mortgage (Debt)
C Amount ...
0
votes
1answer
68 views
In Excel, how to create a VBA that returns a data range from another sheet when data validation = to a certain choice?
I'm trying to return data (from A1:D14) on sheet 4 into A2:D:15 on sheet 1 when I select "A" on my data validation list located on Sheet 1 cell A1.
-1
votes
1answer
237 views
Custom data validation fails even though formula evaluation is “FALSE”
So before I ask my question I apologize for the title wording. I was unable to find a generic wording that fits my problem.
I have a table which is given in Table.JPG.
I have custom data validation ...
0
votes
0answers
181 views
Dependent drop down lists in Excel
In Excel, I'm using data validation to make some dependent drop down lists.
So far it's working fine. Let's say I select a category (fruits) in A5, then depending on my selection on A5, I get a drop-...
