Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have got a workbook and you want to save each worksheet as different workbook - You can adopt following method to do so if worksheets are not many in number.

1. Right click on a worksheet tab
2. Move or Copy
3. Select new book
4. Save this new workbook
5. Do it for all 20 worksheets.

(more…)

Tue 22 Nov 2016

Solution - Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Tuesday, November 22nd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 54 - Make a Sequence like A B C D E F......

Put below formula as array formula in a cell and drag down

=IFERROR(CHAR(MATCH(ROWS($1:1),(ROW($1:$26)*(ROW($1:$26)+1))/2,0)+64),"")

(more…)

Sat 19 Nov 2016

Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

By |Saturday, November 19th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , , |1 Comment

We made ourselves familiarized with Caesar's Shift in Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher and made its decrypter in Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter. We also made a fully functional encrypter in Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher based on VBA solution as formula based solution has its own limitation.

The challenge before you is to write a VBA function to create a fully functional Caesar's Shift Cipher Decrypter. This should take cell or text as first argument and amount of shift as second argument to generate the Decrypted Text.

1

Hence, to generate answer in A3, we have called the function as =DecryptCS(A2,C2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The Excel file related to this challenge can be downloaded from Challenge 56 – Cryptography Challenge 5 – Decryption - Fully Functional Caesar’s Shift Cipher

The answer to this challenge would be published after a month i.e. on 19-Dec-16.

Sat 19 Nov 2016

Downloads 09 - Template 09 - Stakeholder Register

By |Saturday, November 19th, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , , |0 Comments

< The template can be downloaded from Template 09 - Stakeholder Register >

This is a fairly detailed template which you can shorten if you wish. But the recommendation is to use the full template to get complete benefit from Stakeholder Analysis.

1

Fri 18 Nov 2016

Article 43 - Optimize, Troubleshoot and Improve Performance in Excel (non VBA)

By |Friday, November 18th, 2016|Categories: Articles|Tags: , , , , , , , |0 Comments

Below points will help you in following to optimize your excel sheet

  1. Reduce time taken to calculate / recalculate
  2. Reduce time taken to open / close workbook
  3. Reduce file size

Below points may not be valid in many circumstances so you will have to see the suitability of a recommendation but most of the time, these will be valid. Also, in this article we would not concentrate on “WHY” but “WHAT”.

(more…)

Sat 12 Nov 2016

Tips & Tricks 149 - Battery Charge Remaining

By |Saturday, November 12th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
(more…)

Sat 12 Nov 2016

Excel Quiz No. 47

By |Saturday, November 12th, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 47

Quiz number 47

Tue 08 Nov 2016

Solution - Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter

By |Tuesday, November 08th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 53 - Cryptography Challenge 4 - Caesar's Shift Cipher Decrypter.

Put following formula and drag down

=IFERROR(CHAR(IF(CODE(LOWER(G2))-$B$3<97,CODE(G2)-$B$3+26,CODE(G2)-$B$3)),"")

The Excel file containing the solution can be downloaded from Solution - Challenge 53 - Decryption - Caesar's Shift Cipher

Sat 05 Nov 2016

Challenge 55 - Make an Alphabetic Triangle

By |Saturday, November 05th, 2016|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge before you is to write a formula which can be dragged down, left and right to make the below triangle.

(more…)