All about Web and Desktop Application
This is VB code which will auto sort all sheets in the active workbook after execute it. It is easy for us to locate sheets when we have many sheets in our workbook.
Open the workbook for which we want to sort the sheets and press Alt + F11 to open the Visual Basic Editor, and select Insert > Module to insert a module. Copy and paste the following code in module just created.
Popularity: 8% [?]
You may sometimes forgot your password of your Microsoft Excel sheet or workbook, or the original author (usually ex-colleague) lock the workbook and didn’t unlock it for next user, the marco below will unlock your sheet or workbook not matter what your original password is. However, it will NOT report the original password.
1. First open your protected excel file
2. Launch the Visual Basic Editor
3. Copy and paste the following VB Code
Popularity: 100% [?]
Sum a range that contains error values
The SUM function in Microsoft Excel doesn’t work when you try to sum a range that contains an error value such as #N/A. This example shows you how to sum the values in a range named “Data” that contains errors:
=SUM(IF(ISERROR(Data),"",Data))
The formula creates a new array that contains the original values minus any error values. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns one value if a condition you specify evaluates to TRUE, and another value if it evaluates to FALSE. In this case, it returns empty strings (“”) for all error values because they evaluate to TRUE, and the remaining values from the range (Data again) because they evaluate to FALSE, meaning that they don’t contain error values. The SUM function then calculates the total for the filtered array.
Popularity: 17% [?]
This article was written by Ashish Mathur, a Microsoft MVP (Most Valuable Professional). Visit the Microsoft MVP Web site for more information.
In the following article I explain a formula that extracts numbers from strings in the following types of situations:
abc123 or 678sfr.abc15tni.Problem
How to extract the numeric portion of an alphanumeric string. For example: If cell A1 contains the string “abc123″, return the value 123 in cell B1.
Solution
The underlying logic here is to search for and return the first number in the alphanumeric string, and then return only the numbers that follow.
Popularity: 24% [?]
This article was adapted from MrExcel.com. Visit the MrExcel.com Web site for more tips and information.
Conditional formatting in Excel allows for three conditions. However, by setting up conditions correctly — from least to most restrictive — you can really allow for four conditions, when the default formatting of the cell is considered. For example, say you want to add color to your data like so:
| If cell value is | Fill with |
|---|---|
| Less than or equal to -25 | Red |
| Between 0 and -25 | Yellow |
| Between 0 and 25 | Green |
| Over 25 | Black |
Popularity: 9% [?]
Subscribe in a reader
Or, subscribe via email: