Sat 23 Dec 2017

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, December 23rd, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

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
5. Copy and Paste the below code in the module
6. Save your file as .xlsm

7. Call your macro as

=SumColorCells(A1:C6,255,255,0)

In place of A1:C6, your can put your range.

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

1

1

---- Macro by E for Excel ----

 

Sat 03 Sep 2016

Tips & Tricks 144 - Enter the Last Save Date and Time

By |Saturday, September 03rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

1. Open your workbook and ALT+F11
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the Macro code given
5. Save your file as .xlsm

To get Last Save Date, enter following in a cell
=LastSaveDate()

To get Last Save Time, enter following in a cell
=LastSaveTime()

To get Last Save Date & Time Both, enter following in a cell
=LastSaveDateTime()

Note - You will have to format your result cells appropriately in Date / Time / Timestamp format

< Download the workbook illustrating the same Last Saved Date Time >

 

 

Tue 01 Mar 2016

Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm

By |Tuesday, March 01st, 2016|Categories: Solutions|Tags: , , , , , , , , |1 Comment

Below is a possible solution to the challenge - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.

Enter the below formula as Array formula

=MOD(SUM(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)
=0,0,MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)*2-1,9)
+1)+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,2)),10)=0

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the above solution can be downloaded from Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.

Sat 30 Jan 2016

Challenge 35 - Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm

By |Saturday, January 30th, 2016|Categories: Challenges|Tags: , , , , , , , , |1 Comment

This time challenge is to come up with a formula (or a VBA function though formula is preferred) to give the result of TRUE or FALSE if a 16 digit credit card number can be validated or not using Mod 10 algorithm. The credit card numbers will be stored as Text as Excel can't contain more than 15 significant digits in numeric format.

Below is the way Credit Card Numbers can be verified.

1. Start from the rightmost digit and multiply every odd position digit by 1 starting from rightmost. Hence, 1st, 3rd, 5th....15th digits from rightmost should be multiplied by 1.
2. Start from second rightmost digits and multiply this 2. Multiple every even position digit by 2 starting from 2nd rightmost digit. Hence, 2nd, 4th, 6th....16th digits from rightmost should be multiplied by 2.
3. Perform numerological sum on digits which we got in step 2 if result is > 9.
4. Add the results of step 1 and step 3.
5. This result should be a multiple of 10 if the credit card number is valid.

Look at the below example (Row 1 has credit card number. The numbers are in different cell just to make our understanding correct. The formula / VBA function which you will make will be for all 16 digits in one cell only) -

1

Hence, if cell A1 contains following credit card numbers, your result should be as follows for following sample numbers -

5026209217581350 - TRUE
7006688888881300 - FALSE
4406212008581350 - TRUE
6809008888881300 - TRUE
6839808008881306 - FALSE

The result would be published after one month i.e. on 1-Mar-16.

Sat 03 Oct 2015

Tips & Tricks 111 - Extract URLs from Hyperlinks

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster

Now, how to extract this. If there are very few entries like this, you can simply right click the cell > Edit Hyperlink > Copy the URL

But, you have a bunch of entries like this and you need to extract the URLs. For this, the only method is to use a simple piece of code. Don't be frighten even if you have never touched VBA before. Simply follow the following steps -

1. ALT+F11 or Right Click on tab name > View Code to open VBA window.
2. Now locate your workbook on the left side in Project Explorer window though it should be visible by default.
3. Right click on your Workbook Name in Project Explorer window > Insert Module
4. Double click on the module inserted and copy and paste following code

5. Now, in your sheet you can use GetURL as a function like a regular function. If your Hyperlink is in A1, you can write =GetURL(A1) to extract

Credit - http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/

Sat 08 Aug 2015

Excel Quiz 18 - Excel Shortcuts Quiz - Part I

By |Saturday, August 08th, 2015|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 18 - Quiz on Excel Shortcuts

This is a quiz on shortcuts in Excel. We use shortcuts in Excel very often like CTRL+C for copy, CTRL+V for pasting etc. Let's test our knowledge of Excel shortcuts.

Sun 19 Jul 2015

Article 18 - LTRIM and RTRIM through Excel Formulas

By |Sunday, July 19th, 2015|Categories: Articles|Tags: , , , , , , , , |0 Comments

So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel functions only.

Before I delve into LTRIM and RTRIM, let's recap TRIM.

TRIM, basically, removes all spaces from your string and if there are more than one space between your words, it will convert them into only one space. So, it removes all leading spaces, all trailing spaces and all spaces between words except one. If there is only one space between words, it will leave that space untouched.

(more…)