About eforexcel

This author has not yet filled in any details.
So far eforexcel has created 452 blog entries.
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

 

Mon 16 Apr 2018

Excel Quiz 55 - Crossword VIII

By |Monday, April 16th, 2018|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again after a long time. 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 Sep-17 eforexcel.com Crossword

The answer key can be downloaded from here Sep-17 eforexcel.com Crossword Answers

(more…)

Tue 20 Feb 2018

Solution - Challenge 69- Need Help in Not Showing Comments

By |Tuesday, February 20th, 2018|Categories: Solutions|Tags: |0 Comments

Below is a possible solution to Challenge 69 - Need Help in Not Showing Comments

These are not comments but Data Validation Messages..

Select the cell where pop up message is appearing > Data tab > Data Validation > The pop message is in the Input Message > Either you can remove messages or just say clear all.

To remove them in block -
1. Select rows 1 to 7 > Data tab > Data Validation > Clear All
2. Select rows 20 to 22 > Data tab > Data Validation > Clear All

Sat 20 Jan 2018

Challenge 69 - Need Help in Not Showing Comments

By |Saturday, January 20th, 2018|Categories: Challenges|Tags: , |4 Comments

Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these.

The workbook can be downloaded from Challenge 69 - Comments

The solution to above challenge will be published after a month i.e. on 20-Feb-2018.

Tue 16 Jan 2018

Solution - Challenge 68 - Generate a Particular Sequence

By |Tuesday, January 16th, 2018|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -

This consists of 2 logic.

  1. Generate Triangular number. The formula for which is n*(n+1)/2
  2. Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

=SUBSTITUTE(ADDRESS(1,(ROW(1:1)-1)*ROW(1:1)/2+1,4),1,"")

Sat 16 Dec 2017

Challenge 68 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

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

Sat 09 Dec 2017

Solution - Challenge 67 - Find Duplicates and Show the Count

By |Saturday, December 09th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Find Duplicates and Show the Count

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from Solution - Find Duplicates and Show the Count

Sat 09 Dec 2017

Tips & Tricks 167 - Shortcut for Refresh All Pivot Tables Not Working

By |Saturday, December 09th, 2017|Categories: Tips and Tricks|Tags: , , , , |0 Comments

To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task.

But on most of the computer this shortcut may not work. The reason is most of the CTRL+ALT+Fn shorcut keys are reserved by Intel HD Graphics card. You will need to de-reserve this key combination.

Control Panel > Intel HD Graphics (or anything related to Graphic Card, you can put Graphics in Search of Control Panel) > Options and Support > Disable the hot keys even though CTRL+ALT+F5 is not listed there.

1

Sat 25 Nov 2017

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA|Tags: , , , , , |1 Comment

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

(more…)

Sat 18 Nov 2017

Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday

=TEXT(A1&"Jan2017","dddd")

To show only 3 characters of the Weekday Name

=TEXT(A1&"Jan2017","ddd")

You can add a number to A1 if you want to show some other Weekday Name

Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")