Sun 02 Jun 2019

Excel Quiz 57

By |Sunday, June 02nd, 2019|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 57

A general quiz on Excel

Wed 15 May 2019

Downloads 20 - Excel based Consolidated Minutes of Meeting (MOM) Template

By |Wednesday, May 15th, 2019|Categories: Downloads|Tags: , , |0 Comments

Download Link - MOM_Cumulative

While earlier MOM template was or individual meetings, this MOM template can be used for all meetings held. Advantage is that you have running records of attendance, discussion and action items. (more…)

Mon 15 Apr 2019

Tips & Tricks 170 - Convert your formulas to Absolute References

By |Monday, April 15th, 2019|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If your sheet has plenty of formula and you want to convert them into Absolute references i.e.

One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations. (more…)

Sun 03 Mar 2019

Tips & Tricks 169 - Calculate Previous Working day if date is of Weekend / Holiday

By |Sunday, March 03rd, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate Previous Working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 8-Mar-19 as the previous workday. (more…)

Sat 23 Feb 2019

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|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 ----

Function SumColorCells(Rng As Range, R, G, B)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
            SumColorCells = SumColorCells + Cell
        End If
    Next Cell
End Function

 

Wed 16 Jan 2019

Solution - Challenge 68 - Need a Data Validation Formula

By |Wednesday, January 16th, 2019|Categories: Solutions|Tags: |0 Comments

Below is a proposed solution to the Challenge 68 - Need a Data Validation Formula

=IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(--RIGHT(A2,4)=2018,AND(--LEFT(A2,2)>=1,
--LEFT(A2,2)<=12),IF(AND(--RIGHT(A2,4)<=2021,
--RIGHT(A2,4)>=2019),AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=4)))),FALSE)

Sun 16 Dec 2018

Challenge 68 - Need a Data Validation Formula

By |Sunday, December 16th, 2018|Categories: Challenges|Tags: |1 Comment

Need a data validation formula for following assuming you are putting data validation in cell A2.

The user should be able to enter a date in following format

  1. MM/YYYY for the year 2018
  2. QQ/YYYY for the years 2019 to 2021

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

Sat 17 Nov 2018

Downloads 19 - Excel based Minutes of Meeting Template

By |Saturday, November 17th, 2018|Categories: Downloads|Tags: , , |0 Comments

Download link - MOM_Individual

This is a Minutes of Meeting (MOM) template which can be used for individual meetings. Most of the MOM templates are based on Word format whereas this is based on Excel template. (more…)

Sun 28 Oct 2018

Excel Quiz 56

By |Sunday, October 28th, 2018|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 56

A general quiz to test your Excel awareness

Fri 06 Jul 2018

Tips & Tricks 169 - Get the Source of a Pivot Table

By |Friday, July 06th, 2018|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
5. Copy & Paste the below code in this module

Now, you can call this function like

=GetPivotRange(E5)

Where E5 is any cell in your Pivot Table

' *** Macro by eforexcel.com *****
Function GetPivotRange(Cell As Range) As String
    GetPivotRange = Application.ConvertFormula(Cell.PivotTable.SourceData, xlR1C1, xlA1)
End Function