Mail Through Excel

       

To:	itsd.india@vodafone.com
CC:	itsd.india@vodafone.com
Subject	Yes I'm Happy-INC000014624057
body1	Action:'I am Happy'
body2	Note: Please do not edit the subject line of this autogenerated Mail

    =HYPERLINK("mailto:"&C2&"?                            CC="&C3&"&subject="&C4&"&body="&C5&"%0A"&C6&"%0A"&C7,"MSW Email")

Unlock The Password protected Excel sheet

In the Above White Space Enter the below Code. Do not change the code just copy paste:

Sub PasswordBreaker()
    'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
        MsgBox "One usable password is " & Chr(i) & Chr(j) & _
            Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
            Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
         Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

Now Click on the Run Button or press F5:

Allow Cell Drag and Drop option in Excel

Allow Cell Drag and Drop option in Excel 2010?

Sometime back while working on excel 2010, it seems by mistake I disabled the option to use “cell drag and drop”. This feature allows you to drag the mouse and copy cells. If this option is disabled, your mouse pointer will never change to move pointer on selecting a particular cell.

Ok, here is what I mean. In the illustration shown below, mouse pointer is getting changed to move pointer as soon as I bring it to the edge of a cell.

There is some info available on the net for Excel 2003 and the earlier versions ie  by going to Tools > Options > Edit and checking “Allow cell drag and drop”, you can enable this option but there is no information regarding the same for Excel 2007 and Excel 2010. So how can you enable this option in 2010?

1. Go to File > Options > Advanced > Enable file handle and cell drag-and-drop
2. Check this box.
3. Click OK and you’re done

Count if

In Excel 2010 by using COUNTIF conditional logic, you will be able to count occurrence of data and show the result if the condition is met. It enables user to use a condition that contain two arguments (range,criteria), that would be applied on data which yields counting results, only if specified criteria isTRUE. Thus facilitating user to create a group for certain type of data that falls into specific category. This post explains simple usage of COUNTIF logic.

Launch Excel 2010, and open a datasheet on which you need to apply formula with COUNTIF function

=COUNTIF(range, criteria)

=COUNTIF(A2:A34,"SINGH COMMUNICATION")

If you want to Count Singh Comm

=COUNTIF(A2:A34,"SINGH COMMUNICATION")  Ans :- 3

Sumif With Common Word in Range

31

=SUMIF($B$3:$B$22,"=?*is*?",$D$4:$D$24)

It will sum all cells in the range $D$4:$D$24where the word "is" exist in the range $B$3:$B$22.


Enjoy It

IF

How the IF function works

The Excel IF function checks to see if a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The IF function we are using in this tutorial asks if the value in column A is greater than the value in column B. If it is, the IF function will place the statement "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D.

Our IF function will be entered into cell D1 and it looks like this:

=IF(A3 > B3,"A is larger","B is larger")

Note: the two text statements "A is larger" and "B is larger" are enclosed in quotations. In order to add text to an Excel IF Function, it must be enclosed in quotation marks.

About to Excel SUMIFS Formula

What is SUMIF?

We all know that SUMIF formula helps to findout the sum of all values meeting a condition. So, if you have data like below

and you want to find out how many Spit Bombs you sold (spit bombs, really?) then you can use SUMIF like this:
=SUMIF(B3:B16,"Spit Bomb",D3:D16)

But as you guess, SUMIF has a serious limitation. It can only process one condition at a time. So if you want to find out how many Spit Bombs are sold in North, you have to,

1. Drive down to North and meet the victims to take count of spit bombs sold
2. Use array formulas to SUM based on multiple conditions
3. Use SUMPRODUCT formula

While the first option takes a lot of time, the other two involve writing complex formulas.

So What is SUMIFS Formula?

SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.

So, to get the sum of all the spit bombs sold in North, we just write,

=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”North”)

How Does SUMIFS Formula work?

SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data.

Imagine asking “how many spit bombs Hansolo sold in North region of Planet Naboo between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.

The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,

=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”*th”) to get sum of spit bombs sold in North and South.

So what is the catch?

While SUMIFS seems like all butterflies and berries, there is a catch. It works only in Excel 2007 and above. But with Excel 2010 in the horizon, I have high hopes that finally everyone will be able to enjoy the SUMIFS formula.

Bonus:

Just like SUMIFS, there is COUNTIFS and AVERAGEIFS too in Excel 2007+. I am sure you already know what they mean.