Sun 27 Dec 2015

## Excel Quiz 28 - LOOKUPs - Part II

By |Sunday, December 27th, 2015|Categories: Quizzes|Tags: , , , , , |0 Comments

## Excel Quiz 28 - LOOKUPs - Part II

Part II quiz on LOOKUPs in Excel

Sat 26 Dec 2015

## Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

By |Saturday, December 26th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

```Sub CheckifBlank()
If WorksheetFunction.CountA(Range("A1:A100")) Then
MsgBox "Range is Not Blank"
Else
MsgBox "Range is Blank"
End If
End Sub```

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

```Sub TestBlanks()
If Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100")) Then
MsgBox "All Blanks"
Else
MsgBox "Not All Blanks"
End If
End Sub```

Sat 26 Dec 2015

## Tips & Tricks 123 - Change the Color of Border Lines of Active Cell

By |Saturday, December 26th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |1 Comment

Notice the color of Active Cell which is Green. Few people may like some other color.

Excel doesn't provide a native facility to change this. But Chip Pearson offers an Add-in which provides this functionality and has many custom options also.

http://www.cpearson.com/excel/RowLiner.htm

Sun 20 Dec 2015

## Solution - Challenge 30 – Average Last 5 Numbers in a Range

By |Sunday, December 20th, 2015|Categories: Solutions||0 Comments

Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range.

Use below formula -

=IFERROR(AVERAGE(INDIRECT("A"&LARGE(INDEX(ISNUMBER(A1:A100)*
(ROW(A1:A100)),,),MIN(COUNT(A1:A100),5))&":"&"A"&LARGE(
INDEX(ISNUMBER(A1:A100)*(ROW(A1:A100)),,),1))),0)

The workbook containing this solution is uploaded to Solution - Challenge 30 – Average Last 5 Numbers in a Range

Sat 19 Dec 2015

## Challenge 32 - Convert Matrix into Linear Column - I

By |Saturday, December 19th, 2015|Categories: Challenges|Tags: , , , , , |2 Comments

Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.

The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:D100 range. (Note - There would be no blanks in between the values in the Grid)

Download the workbook related to this from Challenge 32 - Convert Matrix into Linear Column

The solution to this problem will be published after a month i.e. on 18-Jan-16.

Sat 19 Dec 2015

## Article 32 - Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)

By |Saturday, December 19th, 2015|Categories: Articles||1 Comment

Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here - 26-Dec-15 and 27-Dec-15) and any holiday (here - 25-Dec-15). Below is the detail calculation for this -

Sun 13 Dec 2015

## Excel Quiz 27 - Crossword VI

By |Sunday, December 13th, 2015|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Crossword 6

The answer key can be downloaded from here Excel Crossword 6 Answers

If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here  Excel_CW_6

Sat 12 Dec 2015

## Tips & Tricks 122 - Always Open a Specific Worksheet when Workbook is Opened

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA||0 Comments

This can only be accomplished through VBA.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Put following code in ThisWorkbook (Replace Sheet Name appropriately)

```Private Sub Workbook_Open()
Worksheets("Sheet Name").Activate
End Sub```

Sat 12 Dec 2015

## Tips & Tricks 121 - Macro to Protect / Un-protect All or Selective Sheets

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

```Sub ProtectAll()
Dim Ws As Worksheet
Dim Pass As String, Answer1 As String, Answer2 As String
Answer1 = MsgBox("Do you want to Protect Selective Sheets", vbQuestion + vbYesNoCancel)
Select Case Answer1
Case vbCancel: Exit Sub
Case vbYes
Pass = InputBox("Input Your Password to Protect Sheets", "Password")
For Each Ws In Worksheets
Answer2 = MsgBox("Do you want to Protect " & Ws.Name, vbQuestion + vbYesNoCancel)
Select Case Answer2
Case vbYes: Ws.Protect Password:=Pass, UserInterFaceOnly:=True
Case vbCancel: Exit Sub
End Select
Next Ws
Case vbNo
Pass = InputBox("Input Your Password to Protect Sheets", "Password")
For Each Ws In Worksheets
Ws.Protect Password:=Pass, UserInterFaceOnly:=True
Next Ws
End Select
End Sub

Sub UnProtectAll()
Dim Ws As Worksheet
Dim Pass As String, Answer1 As String, Answer2 As String
Answer1 = MsgBox("Do you want to Unprotect Selective Sheets", vbQuestion + vbYesNoCancel)
Select Case Answer1
Case vbCancel: Exit Sub
Case vbYes
Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
For Each Ws In Worksheets
Answer2 = MsgBox("Do you want to Unprotect " & Ws.Name, vbQuestion + vbYesNoCancel)
Select Case Answer2
Case vbYes: Ws.Unprotect Password:=Pass
Case vbCancel: Exit Sub
End Select
Next Ws
Case vbNo
Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
For Each Ws In Worksheets
Ws.Unprotect Password:=Pass
Next Ws
End Select
End Sub```

Sun 06 Dec 2015

## Solution - Challenge 29 – Reverse (Flip) a Number String

By |Sunday, December 06th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 29 – Reverse (Flip) a Number String

You may use below formula to reverse a number string -

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
*10^ROW(INDIRECT("1:"&LEN(A1)))/10)