Hot answers tagged

17

In Excel the standard line break Alt + Enter character is ASCII 10. From the look of your screenshot it appears there is another character, probably ASCII 13. To find these, in the standard Find (or Replace) dialog, in the Find What field, use the standard method of entering ASCII character codes: hold down Alt and type (on the numeric keypad) the ...


13

Click the cell you want to restrict value for Go to Data -> Validation. The Data Validation dialog box opens In the Allow dropdown select List In the Source text box enter Yes,No Note: This will also work for more than more than two possible options; simply separate additional desired options with a comma. Now your cell will have a dropdown with Yes and ...


7

If you want to do this with a formula, it would be FIND(A3,CHAR(10),... This is useful if trying to break apart an address block.


7

If you press Ctrl + J while in the Find dialog, you will be able to search for those characters.


6

See the Forms section in Excel Help for the topic "Add a list box or combo box to a worksheet". From that page (referring to a Form control list box): Note: If you set the selection type to Multi or Extend, the cell that is specified in the Cell link box returns a value of 0 and is ignored. The Multi and Extend selection types require the use ...


5

Yes, from this example ... Select a range of cells, for example, A2:A20. Select Data -> Data Validation (in Data Tools Group). In Data Validation dialog box, select Settings -> Allow -> Custom. In the Formula box, enter the following formula: =COUNTIF($A$2:$A$20,A2)=1 Select the Error Alert tab. In the Title box, enter: Duplicate Entry. In the Error ...


4

There is an Excel VBA Validation object, associated with the range. See code: With Range("e1").Validation .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertInformation, _ Minimum:="5", Maximum:="10" .InputTitle = "Needs Wholenumber" .ErrorTitle = "Integers" .InputMessage = "Enter an integer from five to ten" .ErrorMessage = "You must enter ...


4

This seems dangerous, but I can't see any problem with it. Basically if you change anything in Foobar, it searches every cell on the sheet that has Data Validation. If the DV points to Foobar and the value isn't in the list, then it must have been the value that was changed. It worked with my limited testing. Let me know if you see any flaws. Private ...


4

You can use INDIRECT function in Excel. First create a named list consisting of values TypeA, TypeB, ... Let's call it "type" Then create named lists for each group of subtypes. You can hide these lists later if you want. Now to make them dependent, first select the cell in which you want the user to select the type and create a drop down list for ...


4

I'm using Excel 2010 so I'm not sure where the equivilent option is in Excel 2003. Select the cells you want to restrict. Format the cells with the appropriate number format "hh:mm". In the "Data" ribbon (menu, maybe?), select "Data Validation". Under "Settings", "Validation criteria", set: Allow = Time Ignore blank = Yes Data = between Start time = 0:00 ...


4

In the cells you want to check for even set the data validation to custom, then in the formula =NOT(MOD(A2,2)) check odd numbers in the same way with the formula =MOD(A3,2) of course adjusting the cell address, the style, and Error Alert you want in response


4

You can do this with Excel's built-in Data Validation feature. It will work as an in-cell dropdown which will also allow you to type in a value not included in the dropdown. You'll need to do the following: Somewhere in the workbook, enter the data you want to appear in the dropdown. In my example, it's on the same sheet as the dropdown, but it doesn't ...


4

You can put them in as a comma separated list: true,false yes,no 2,4,6,8 with space,without etc.


4

Unless you mean something else, no need to wrap in quotes, or use the "=" sign


3

This page has instructions on how to do it, in detail. I will quote a section though:


3

not long after I posted this, Google released Google Refine, which appears to have all of the features I was imagining, and then some. http://code.google.com/p/google-refine/ I got a new job right when I found out about refine, or I certainly would have tried it out on some real-world data. at the moment I haven't used it myself, so I can't comment on its ...


3

Try these: Method 1: Checking for any number Highlight the cell you wish to apply validation to. For this example, let's assume you want the user to enter a value in A1. Go to Data > Data Validation Set to Custom Enter the formula below. =ISNUMBER(A1) where A1 is the cell you are validating. Do not include $ so you can copy/paste the validation to ...


3

You must enter a formula that returns true for valid and false for invalid. Note that even if you are checking the content of the selected cell itself you still need to explicitly reference it. So in your example the formula would be =B2 <> 28000 The above works because the <> operator compares the value of B2 to 28000 and returns true if they ...


3

The short answer is 'No'. The Data Validation feature can be used as a quick-and-dirty replacement for a genuine drop-down/combobox, but it is limited in the amount of features and options.


3

That's because that's not how data validation works in excel. You need to use an active X control. Refer to the Contextures data validation page in general and her ComboBox Data Validation and ComboBox Depend examples in particular. Update If you do use her examples, replace the TempCombo_KeyDown event handler with TempCombo_KeyUp. At least on my excel ...


3

You can not reliably check that a particular address is valid because many mail servers will not tell you directly during the SMTP conversation. Many are explicitly configured not to respond with "user/mailbox does not exist" errors in order to stop outsiders being able to derive a list valid addresses. Also the SMTP server you connect to may just be a relay ...


2

I found the culprit! The item is a Combo Box (ActiveX Control). It can be manipulated when in Design View which can be accessed via: In Office 2003 :: View -> ToolBars -> Control Toolbox -> Design Mode In Office 2007 :: Developer Tab -> Design Mode


2

Also, just a side note.... when referring to particular cells in a column (like in your example) you would refer to them as B1:B20. To refer to the whole column, you can use B:B. That describes an array of all data in the B column. Hope that helps.


2

I believe GnuPlot can do this There are also Perl modules that do this sort of thing: use Graph::Easy; my $graph = Graph::Easy->new(); $graph->add_edge_once ('Bonn', 'Berlin'); print $graph->as_ascii( ); # prints: # +------+ +--------+ # | Bonn | --> | Berlin | # +------+ ...


2

In the VBA editor, create a new Module in your workbook (you'll need to save it as .xls or .xlsm, not .xlsx) and paste code like this: Function RegEx(Pattern As String, TextToSearch As String) As String 'vv Dim RE As Object, REMatches As Object Set RE = CreateObject("vbscript.regexp") With RE .MultiLine = False .Global = False ...


2

No such tool exists to magically recover corrupted data unless you have sufficient parity/backup copies of the data in question. @grawity indicates tools like PAR2, QuickPar, and WinRAR can do what you want. To work properly those tools will build parity data into the archive so recovery will (hopefully) be possible if some data is corrupt. I am a long ...


2

Select whole column H and use this formula in conditional formatting =OR(H1<0,H1>100) That will adjust automatically for each row (you don't need INDIRECT here) Unless H1 is a number that satisfies the conditions you don't need to exclude H1, if it is then change the "applies to" range as Raystafarian suggests


2

SHA1 is simply a mathematical calculation on the contents of files, and SHOULD be unique all the time. As a file changes, the SHA1 hash changes (hash=result). What you would do (I don't know the specific programs): Run the SHA1 program and get a result back Copy the result to a file on a separate media (i.e. flash drive) Run the copy command of your choice ...



Only top voted, non community-wiki answers of a minimum length are eligible