Questions tagged [data-validation]
The data-validation tag has no usage guidance.
188
questions
0
votes
0answers
7 views
Access deleted several records when data type changed. How to find which ones caused the errors?
I'm trying to work with an old Access database. It has a column formatted as Short Text that I need to change to numerical data.
To attempt to change the data type, I opened the Design View and ...
1
vote
1answer
19 views
Can I propose a dataset from copyrighted images?
I would like to use copyrighted images to analyse an AI model I am building. Images are used for non-commercial research/educational only.
I intend to make the dataset publicly available via URL links ...
0
votes
1answer
21 views
Limit in excel data validation source code
I have an excel file with two sheets. The second sheet (Report) contains data validation cells based on the first sheet (Data). From the second sheet, the drop-down list that displays in the Select ...
0
votes
1answer
37 views
Sorting between start and end times for a set of projects
I need a way to nest projects that I am working on. The list is so large that it is impossible to do it by hand. I am attaching an example of what I am looking to do.
My starting data is what you see ...
0
votes
1answer
31 views
Excel Data Validation- Numbers only and specific text
I am trying to use data validation in excel to allow a user to only input a number within a specific cell, otherwise, they can also type "unknown" within the same cell. Right now I'm using ...
0
votes
1answer
52 views
How to Update Occurrences in a List of Excel
I am trying to build a tool tracking application where users can check in/check out tools at the required quantity. So far I have written the code for the check-in of tools which has Name, Date, Tool, ...
0
votes
0answers
21 views
Syncing data in sheets on Excel & Finding errors between the two
I have been tasked with comparing two sets of system data in two sheets in Excel. The information is supposed to be the same but they aren't (some missing in one sheet, some errors). I must find the ...
0
votes
2answers
121 views
Check if parent row has corresponding values
I have a table in an Excel spreadsheet that has hierarchical rows:
(Hint: The data below can be copied from the EDIT-view in Stack Exchange and pasted into Excel. This method will result in proper
...
0
votes
1answer
69 views
How to make data validation with unique formulas in Excel?
I am trying to keep the formulas of a worksheet intact, making sure that they will not be removed by a mistake. Using data validation for it.
This is what I am trying:
It works, but the cell content ...
0
votes
1answer
104 views
Excel conditional data validation without named tables?
I created a conditional data validation for my device groups using "named tables" like in the example shown here:
Then on cell B5, I went to data validation, selected list, and added formula =...
0
votes
0answers
12 views
MS Office 2016 Excel Auto filling a drop down menu in one cell based on another cell drop down option selection
This looks simple, but couldn't figure out how to do it.
I have two drop down cells A1 and A2, both have same drop down options.
A1 options are "Yes", "No".
A2 options are "Yes", "No"
Question is: ...
0
votes
1answer
31 views
How to have a sum of cells not exceed another cell?
Cell A23 get's its value from cells A19:A22.
Cell A23 cannot be higher than the number already in cell H2.
What formula would I use in data validation to accomplish this?
2
votes
1answer
36 views
Data Validation - Two Cells Cannot Have the Same Value
Cell C2 and Cell G2 cannot both be greater than 0 at the same time.
If C2 or G2 has greater than 0 on it's own that's fine.
I have an error message set up saying these two cells cannot both, at the ...
0
votes
0answers
5 views
Microsoft Access Show data entries that violate Field Validation Rule
When changing the validation rule for a field (or clicking run validation tests in design view) Microsoft Access will prompt me with a message box in case some existing data violates the new rule. I ...
0
votes
2answers
45 views
Compare 2 Lists of E-mail Addresses
How can I go about performing a comparison between 2 lists of e-mail addresses? Specifically, I am looking to find what e-mail addresses are not present in one list vs the other.
0
votes
1answer
49 views
How would I find how many rows in excel contain multiple unique values?
I have a list of all buyers in our state with their account number and the zip code they live in. I'm tasked with finding out how many buyers live in each zip code.
|Account ID|Zip |
| 13 ...
0
votes
0answers
22 views
Data Validation - Validate An Entry Based On Another Cell
I am looking for a data validation formula which allows me to do the following:
In my excel worksheet I have two columns, and thousands of rows :
Column A is 'Total number of cars',
Column B is '...
0
votes
0answers
17 views
Excel - Drop down list that allows non-listed values
I'm looking for a way to have a drop-down list based on existing data BUT that allows me to enter values that are not included.
Let me picture the situation:
I have an evaluation form where we enter ...
0
votes
0answers
26 views
Validated cells copy paste restriction
Restrict paste into dropdown cells in excel
My question comes in after I read the aforementioned article:
While this VBA code allows for validated cells to be blocked so you can't paste over them. ...
1
vote
1answer
23 views
Custom data validation
Using the custom data validation rule, is it possible to only allow values that follow the below criteria:
Length = 7
First 3 digits = "ISO"
Digits 4,5 & 6 must be numbers
I currently allow any ...
1
vote
0answers
63 views
Excel - Manual Column(s) Beside Imported Data
In excel, how do you properly mix 'manual' data and imported data in a sheet / data range?
Requirement
For context, my particular use case is a sort of bare-bones data "triage" tool in excel.
Ideal ...
0
votes
1answer
57 views
How can I assign a numerical value to cell that contains text?
I am trying to assign numerical values to a list of drop down values that are able to be selected via list data validation. I want to display the text, however be able to quantify my results with the ...
1
vote
2answers
65 views
MS Excel. How to get around pivoting data that has blank placeholder columns?
I've been asked to produce a dashboard based on a data set that looks something like the screenshot below. The dates are the column headings and there are blank and placeholder columns in between each ...
0
votes
0answers
25 views
MS Excel. Does it make sense to represent data in such a way?
I have a data set that looks something like this. At the moment, data is sorted by region, and at the end of each section there is a row for Region averages for all the months.
However, I'd like to ...
1
vote
1answer
25 views
Adding multiple validations to a cell
I'm adding some validations to a form in excel.
The rule is displayed when the cell is selected, and the cell must highlight red when it doesn't match the criteria.
This is usually fine except I ...
0
votes
0answers
115 views
VBA - Unable to delete objects if using Data Validation
Newbie here, but I've exercised about all the Googling I can do to find the solution and I just have not found one anywhere. Wondering if any expert VBA guys/gals can help a rookie out...
Problem: ...
0
votes
0answers
24 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
2answers
208 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
12 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
21 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 ...
1
vote
1answer
74 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
12 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
3k 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
154 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
431 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
39 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
433 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
1answer
274 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
30 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
712 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 ...
16
votes
4answers
8k 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
1k 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 ...
1
vote
1answer
83 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
109 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
60 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
89 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
2k 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
64 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
757 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 below ...
