Tag Info

Hot answers tagged

11

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


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


5

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.


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


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

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

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


3

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


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

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

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

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


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

This is a known error in Excel 2010 compatibility checker. It happens when you try to save the sheet in xls format and you have data validation formulas on a sheet that is not the active sheet when you perform the save. The error is incorrect and there is no loss of functionality. Refer to Microsoft knowledge base article 2757267. To verify this, ...


2

You can make a checksum of the disk contents and verify with a previous one to test whether disk contents have changed.


2

I would consider using Access instead of Excel. The behavior you want is built in to its basic functionality.


2

MS Access really is the better option but it can be done in Excel. You could keep all the records in the same sheet regardless of their status and then just use a filter to show only the status you want to look at. Doing so, you could quickly bounce between the different statuses. When adding a new record you could take the filter off and sort by ...


2

The parameter C2:D2 forms an array and thus you need to make it an array function. The correct formula would be =(SUM(COUNTIFS('LP - All Properties'!G:G,C2:D2,'LP - All Properties'!L:L,"Leased")))/SUM(COUNTIF('LP - All Properties'!G:G,C2:D2)) And then here comes to the tricky part - instead of hitting Enter finishing your input, you will need to press ...


2

To allow text only select the Data tab from the ribbon and choose Data Validation and again select Data Validation. Change the Allow drop down to Custom and put this in the formula field: =ISTEXT(A2:A14) Put whatever cell range is relavant to you in the parenthesis. You can also create a custom error alert. To allow only numbers, repeat the steps ...



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