0
votes
0answers
20 views

Select item from drop down menu and run macro

I have a drop down list in a sheet called 'Report', cell B9. The source for this list is in another sheet in the same workbook, 'List' with range 'A1:A32'. When the different items in this list are ...
0
votes
1answer
26 views

Use an user defined function for data validation

I created a named range which is the result of a user definer function. Let say my rage is named List and has three items, if I select three cells and enter =List and press Ctrl+Shift+Enter, I get ...
3
votes
1answer
123 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 ...
1
vote
1answer
29 views

How to have a drop-down in a cell only if certain cells have text

I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2. I ...
1
vote
1answer
52 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 ...
-4
votes
2answers
248 views

Custom Data Validation in Excel 2010 [closed]

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use? ...
1
vote
2answers
166 views

Excel: Reference Dropdown Cell in Countif Formula

I'm trying to substitute text in an excel formula with a cell reference to the text in a dropdown. First formula that yields the correct answer is: =(SUM(COUNTIFS('LP - All ...
0
votes
2answers
456 views

How do I require a cell in one column to have a value if a cell in another column (same row) has a value?

I'm trying to require a value to be populated in column A if column D has an amount in it. Column A can hold any type of value (text or numerical), column D can only be a number. So, for example, if ...
1
vote
1answer
4k 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 ...
0
votes
1answer
2k 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
2
votes
1answer
331 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
1
vote
2answers
485 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") ...
0
votes
1answer
245 views

Can I force MS Excel table column to use the specified pattern?

I need to develop a tool that enforces strict format of some table. The fields in this table follow specified patterns like number:number (11:20. 2:00), agreed date (11/20/2012 but not ...
0
votes
2answers
158 views

Filter Duplicates by count of a character Excel 2007

I have a data list of around 32000 items in column A. Each of these items has many duplicates, some having 1 duplicate, up to about 5 or so max. Each cell has multiple sku's, divided by a /. What I ...
1
vote
1answer
56 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 ...
3
votes
2answers
1k 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 ...
3
votes
2answers
1k 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: ...
0
votes
2answers
222 views

Restricting symbols allowed in cells of Excel spreadsheet

I need to format some Excel cells to allow currency (decimal point) only to be entered. Some employees completing the form enter a colon (:) instead of a decimal point which then results in an ...
2
votes
1answer
539 views

Microsoft Excel validation based on adjacent cell

I have a two adjacent columns named Country and Rating. The Country column has a dropdown to select which country a DVD is being delivered to. It is using a list validation to create the dropdown: ...
2
votes
1answer
6k 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, ...
0
votes
3answers
852 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 ...
8
votes
4answers
32k 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 ...
4
votes
1answer
197 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 ...
0
votes
2answers
1k views

Excel 2007 vs Excel 2010: Data Validation values using Named Ranges and the INDIRECT function

I am running into a bizarre error in Excel that I am hoping someone can help me with. I have a macro-enabled workbook created in excel 2010. On the workbook are a series of radio buttons. When a ...
9
votes
1answer
2k 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 ...
4
votes
2answers
5k 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 ...
0
votes
1answer
1k views

How do I clear cells with list data validation based on another column's value?

I have a test planning worksheet with the following columns: Test Name, Run?, Tester, Tester Location, Environment, etc. The "Run?" column has data validation on for valid values of Yes and No. The ...
4
votes
2answers
318 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?
4
votes
3answers
9k 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 ...
3
votes
2answers
2k 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 ...
1
vote
2answers
3k 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 ...
4
votes
1answer
4k 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) ...
0
votes
2answers
546 views

Validate a cell containing formula

I have validated a cell by data validation. Now, it can hold only values between 0 to 100. if user enters value out of range i.e. below 0 or above 100 a message box appears to inform that value must ...
1
vote
2answers
970 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. ...
0
votes
1answer
95 views

How do I ensure only integers and with no duplicated values can appear in a column in Excel 2007?

How do I ensure only integers with no duplicated values can appear in a column in Excel 2007?
1
vote
4answers
3k views

How to input only unique values in a column in Excel 2007

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't ...
0
votes
1answer
695 views

Using Excel Lookup Function and Handling Case Where No Matches Exist

I'm using Excel to enter data for an event where people register. A high percentage of the registrants will have registered for previous events, so we can their name and ID number. I'm trying to use ...
6
votes
3answers
3k 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 ...
8
votes
1answer
952 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 ...
5
votes
1answer
2k 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 ...
0
votes
1answer
218 views

Statically Display Excel drop-down boxes

I'm looking to present an Excel questionnaire I have written to the board, via either paper or presentation slides. It uses a lot of drop-down boxes, and so I'm looking for any ideas on how one might ...
2
votes
2answers
841 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 ...
2
votes
2answers
8k 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 ...
3
votes
1answer
6k 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 ...
2
votes
1answer
606 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?