Sat 24 Dec 2016

Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page

By |Saturday, December 24th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8......Last Page should say 8 of 8.

The above problem will have to be solved through VBA.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button

 

 

Sat 24 Dec 2016

Downloads 10 - Printable & Pin-up Sheet for Excel Shortcuts

By |Saturday, December 24th, 2016|Categories: Downloads|Tags: , , , , , , , |0 Comments

You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference. - Excel Shortcuts Quick Reference

(more…)

Mon 19 Dec 2016

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

By |Monday, December 19th, 2016|Categories: Solutions, VBA|Tags: , , , , , , , , , , , , |0 Comments

Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter

(more…)

Sat 17 Dec 2016

Challenge 58 - Make a Vedic Square

By |Saturday, December 17th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

Vedic square is below. In this, first row and first column are numbers 0 and 9. Other numbers are populated as per logic which you will need to identify.

More about Vedic Square at https://en.wikipedia.org/wiki/Vedic_square

1

(more…)

Sat 17 Dec 2016

Excel Quiz No. 48

By |Saturday, December 17th, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 48

Quiz No. 48. Only formulas and functions.

Sat 10 Dec 2016

Tips & Tricks 151 - Saving in ODS (OpenDocument Spreadsheet) but Don't Need Excel Warning Message - "Do you want....."

By |Saturday, December 10th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

Question - Sometimes, I may require to work in different Office Suites and to maintain compatibility, I use Open Document Format. But whenever, I save in Open Document Format (ODS - OpenDocument Spreadsheet), I get the Excel warning like below. I don't need this. What is the way out?

1

Answer - This requires a registry hack, hence you need to take the backup of your registry first. Also, if you are not comfortable with modifying your registry, don't proceed.

For backing up your registry and restoring in case of any issue - https://support.microsoft.com/en-us/kb/322756

1. Press Windows +R, type regedit and press Enter.
2. Navigate to the following location:
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
(In case of Excel 2013, replace 16.0 with 15.0, Excel 2010 - 14.0, Excel 2007 - 12.0, Office 2003 - 11.0)
3. Right click on the in the left panel and select New>DWORD.
4. Once new DWORD is create right click on it and select Rename asDisableSaveAsLossWarningOpenDocumentSpreadsheet
5. Set Value data 1 to suppress the warning message.

Mon 05 Dec 2016

Solution - Challenge 55 - Make an Alphabetic Triangle

By |Monday, December 05th, 2016|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to Challenge 55 - Make an Alphabetic Triangle

Put following formula AB4 somewhere and drag to the right.

(more…)

Sat 03 Dec 2016

Challenge 57 - Another Word Challenge - Palindrome or Not

By |Saturday, December 03rd, 2016|Categories: Challenges|Tags: , , , , , , , , , , |1 Comment

We earlier had challenges related to Pangram, Isogram and Anagram. This time, it is about Palindrome. A Palindrome is a word / sentence that reads the same from backward also as it reads from forward. Some of the famous Palindromes are

(more…)