# Questions tagged [data-validation]

The data-validation tag has no usage guidance.

188
questions

**0**

votes

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**2**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**4**answers

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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 ...