Questions tagged [data-validation]
The data-validation tag has no usage guidance.
205
questions
39
votes
1
answer
174k
views
Limit an Excel cell to one of two possible values
I would like to create a cell format for one cell to only allow the value "yes" or "no" for this cell.
How could I do this?
Josemalive
26
votes
4
answers
203k
views
How to search for newline or linebreak characters in Excel?
I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially:
If I hit F2 (to edit) then Enter (to save changes) on each of the ...
- 3,023
19
votes
4
answers
16k
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.
...
16
votes
3
answers
54k
views
How do I create a data validation list WITHOUT using cells?
I have a simple data validation dropdown that I'm trying to do, but I DO NOT WANT to use cells to populate my list.
Something similar to what's below, but this obviously isn't working.
What ...
- 26.4k
11
votes
1
answer
6k
views
Setting a default value for a dynamic cascading validation list in Excel 2010
I have finally figured out how to build a dynamic cascading set of lists in Excel using Data Validation and the INDIRECT function. However, I'm running into a problem.
I would like to set up my ...
- 159
9
votes
1
answer
2k
views
Where is data validation text stored in Excel?
I have an Excel 2003 sheet with lots of different data validation rules.
Is there anyway that they can be viewed other than through the regular data validation dialog?
It would be helpful to export ...
- 3,450
7
votes
1
answer
3k
views
Excel, drop down lists
When I created custom drop down lists in excel, my excel sheets don't trim down to a smaller list when I start typing in the drop down list cell.
For example if my drop down list is for USA States [...
- 1,445
6
votes
4
answers
26k
views
How do I force a user to use a specific format when entering time data?
I need to ensure the users of a particular spreadsheet are entering the time data in military time. I'm finding some folks are omitting the colon, and it's causing problems. Is there a way to force ...
- 71
6
votes
3
answers
6k
views
Excel: Update a value in a named range
I've got some cells in an Excel doc populated from a named range (data/validation/list source=MyNamedRange):
Here, the A1:A3 range is named Foobar
B5:B6 is under data validation with it's source set ...
- 162
6
votes
2
answers
15k
views
Data Validation of a Comma Delimited List
I need a simple way of taking a comma seperated list in a cell, and providing a drop down box to select one of them.
For Example, the cell could contain:
24, 32, 40, 48, 56, 64
And in a further ...
- 63
5
votes
3
answers
17k
views
Excel 2010 data validation warning (compatibility mode)
We have some legacy worksheets that were created in Excel 2003, which are used by LabVIEW-based test automation software. The current LabVIEW software can only handle the legacy .xls format, so we're ...
- 298
5
votes
2
answers
26k
views
show more than 8 items in the Data Validation drop down list in Excel
I have added a simple data validation to a cell but it only displays 8 items in the dropdown.
Excluding the combobox solution and probably involving some WinAPI is there a way to make the drop ...
user222864
4
votes
4
answers
56k
views
Excel data validation to whole column
I want apply data validation list to a whole column in excel 2010. How can I do that with out copying the validation rule
- 173
4
votes
1
answer
10k
views
Multi-value selection in an Excel cell
There's a way to make an Excel cell kind of a dropdown selection box. You would just go to Data->Validation with a cell or a range of cell selected, select list as the data type and specify the list ...
- 1,013
4
votes
2
answers
656
views
Email address validation tool - recommendations/experience? [closed]
I recently came across a tool which will take a list of email addresses, and attempt to cleanse it of the nonsense addresses that people use when wanting to download/sign up for something without ...
- 5,335
4
votes
2
answers
582
views
Entering only Digits in Excel DataSheet Cell
I want users to enter only 1 or 0 in a cell column in Excel 2007. Users should not be able to enter anything else than 1 or 0. How can I do this in Excel?
- 41
4
votes
2
answers
7k
views
Only Add Odd Or Even Numbers To A Cell
I want to create a cell in and Excel spreadsheet that can only have Even numbers entered into it and also one just for Odd numbers. Any ideas what formula I can put into the Data Validation formula to ...
- 71
4
votes
1
answer
286
views
Is there any way to embed tables from excel that have pull down menu
I may not be using the correct terminoloby, but, here goes. We have setup an Excel spreadsheet that is formatted into multiple 8.5 x 11 pages on one tab. The spread sheet has tables with a pull down ...
- 41
4
votes
0
answers
2k
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 ...
- 41
3
votes
1
answer
11k
views
Excel 2007 Data Validation: Validate Against List or Allow User to Enter Custom Value
I know this kind of defeats the purpose, but is it possible to have an Excel 2007 spreadsheet with a cell that has:
Validate against a list that is column in the spreadsheet.
If not in that list, ...
- 389
3
votes
2
answers
14k
views
Excel data validation for decimals, but without a range?
I want to use the Data Validation in Excel to prevent users from entering a non-decimal value, but I want to allow them to enter any decimal value, however you are forced to provide a range or ...
- 2,273
3
votes
2
answers
4k
views
Excel Conditional Formatting
Highlight all non-blank entries in a column, except for the column header, that do no fall in some range.
Here's my attempt:
=AND(OR(VALUE(INDIRECT("H"&ROW()))<0,VALUE(INDIRECT("H"&ROW()))&...
- 197
3
votes
1
answer
7k
views
Custom Validation - Dependent Drop Down Lists
I've two columns in a sheet that are interdependent and I want to use validation, drop-down lists, on both as follows:
Column A (TYPE) | Column B (Sub-TYPE)
------------------------------------------|...
- 173
3
votes
2
answers
2k
views
software tools for cleaning up messy data (flat files, Excel etc) semi-automatically?
I routinely receive files, usually Excel spreadsheets, which contain manually-entered data that have outgrown the utility of spreadsheets. I then clean up these files for import into a database.
...
- 1,429
3
votes
2
answers
5k
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 ...
- 962
3
votes
3
answers
54k
views
Unlocking Locked Drop Down boxes in Excel
I have a couple of drop down boxes on a form that are what I'd like to dub "perma-locked". Every person we've thrown at the form is unable to unlock the drop down.
The drop downs do not show a ...
- 2,406
3
votes
1
answer
2k
views
Can CPU overheating cause data corruption?
Background:
I realized recently thanks to dmesg and syslog that my CPU has been overheating since May. No overclocking. I have an Intel® Core™ i5-2500K, and the stock cooler was simply defective as ...
- 259
3
votes
1
answer
918
views
Best way to visualize graph on a text console?
I looking for a solution to visualize a graph on a standard unix text console. Speaking more precisely I need to visualize a graph of dependencies between files from specified set, those dependencies ...
- 131
3
votes
1
answer
1k
views
Excel "Smart" Dropdown / Data Validation
In Excel, is there a way to create a dropdown menu or data validation field that
Allows the user to enter a value that is not currently on the list (like a combo box)
Save that value in the dropdown ...
3
votes
2
answers
2k
views
MS Excel data validator does not accept call to the custom macro
I have the working VB macro that validates the content. It takes two string parameters (cell value and regular expression) and returns either True or False. It uses CreateObject("VBscript.regexp") ...
- 444
3
votes
1
answer
2k
views
Make dropdown list show hyperlinks
I have a collection of all the different part numbers for different versions of a single part, and each of them is hyperlinked to their specific different drawing in pdf form. I would like to put them ...
3
votes
1
answer
2k
views
Data Validation arrow hidden from freeze panes?
I've been searching with no luck about my issue of the data validation drop-down arrow being hidden by freeze panes.
I have read multiple forum posts about Excel 97 having this as a bug, but fixed in ...
- 76
2
votes
3
answers
136
views
How can I avoid typing in a value which already exists in another range in Excel 2010?
I am working up a basic project tracking spreadsheet in Excel and would like to have two sheets - one which shows my active projects and one which shows my completed/cancelled/archived projects. I ...
- 220
2
votes
1
answer
870
views
Excel validation - can it be used to make sure duplicates are not entered?
I have an excel spreadsheet - is it possible to have validation so that a column cannot contain a value that already exists in the column?
- 1,039
2
votes
3
answers
21k
views
Why does the dropdown button in Excel data validation disappear?
I am creating a dashboard in Excel in which I want to restrict the input to a specific list. I used data validation to do so which works perfectly fine. However, normally the little drop-down button ...
2
votes
2
answers
814
views
How can I validate or format dates in excel
How would I format a date in a table such that the next row cannot have a date that precedes the current row?
For example, I have a table of 2 columns which have column headings "Date" and "Employee"...
- 137
2
votes
3
answers
8k
views
Table data entry - How to always have a blank row at the end of an Excel table
I'm creating a simple Excel spreadsheet for table data entry (table created using the "Format as table" option -- couple of columns, there could be hundred of rows). I would like to always have the ...
- 595
2
votes
2
answers
2k
views
In-cell Validation Drop-down list - widen
I have quite a few cells with Data Validation with options in the list that are long strings. Short of widening the column, is there any way to widen the drop-down list so that the user can see the ...
- 126
2
votes
1
answer
777
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 ...
user1151619
2
votes
2
answers
4k
views
How can I Implement Dynamic Dependent Data Validation List in Excel
Sources on the web show how to implement dependent data validation lists but all I have come across are static and do not update automatically.
I believe whoever might be able to answer this question ...
- 137
2
votes
1
answer
3k
views
Enforcing format input into a cell
I need my spreadsheet to only allow users to input in the format hh.mm/hh.mm for their working pattern e.g. someone works 12.5 hours in a 37 hour week would only be allowed to be input as 12.50/37.00
- 21
2
votes
1
answer
100
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 ...
- 123
2
votes
1
answer
176
views
MS Excel - Data Validation
I am looking for formula i can use in the Data Validation tool in Excel.
There is a type of field i want to apply rules to:
Numeric & Symbols only (no alpha)
I am at a complete loss, tried ...
- 23
2
votes
2
answers
285
views
Linked dropdown lists?
I would like to link 2 dropdown lists in Excel. Searching for solutions always resulted in cascading dropdown lists. What I want is two dropdown lists with the same options. When you change one, ...
- 1,912
2
votes
1
answer
991
views
Can a drop down in Excel show long descriptions when clicked, but only a short code once selected?
I have created a drop down menu in Excel, but the options are long sentences. After someone selected one of the options I want a letter to appear instead of the entire option. How would that be ...
2
votes
1
answer
25k
views
Linking dropdown lists
I would want to link a drop-down list to the values of an adjacent cell. Let say, we have two cells A2 and B2. A2 is a drop-down list with values: Agric, school, sme and housing, while B2, which is ...
- 21
2
votes
1
answer
87
views
Enforce style of named list items in Excel 2010
I have a named list with some values which have their own style (e.g. different colors):
However when I apply that list as a data validation and select some values, the styling disappears:
:
How can ...
- 458
2
votes
1
answer
4k
views
Need to recover data from a poorly burnt 8.5GB dual-layer DVD - Cannot copy half the data from the disc
Synopsis:
I had a series of backup CDs that I had burnt some time ago. In order to consolidate the many discs I copied them all to my hard drive in separate folders, compressed them into .RARs (at ...
- 589
2
votes
1
answer
76
views
Copy text into Excel, Fixed Width, where data starts every 40 rows
I have data from a text file I want to import into Excel. The text data is grouped in blocks of 40 rows with headings. After about 12 rows the new set of data starts again with headings. There are ...
2
votes
1
answer
2k
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 ...
