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

Sun 28 Oct 2018

## Article 48 - Show Pictures Conditionally

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

Sat 15 Sep 2018

## Tips & Tricks 170 - Convert your formulas to Absolute References

By |Saturday, September 15th, 2018|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.

Best way is to use VBA method.

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 Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

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: , |3 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 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||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 -

Sat 06 Jan 2018

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

By |Saturday, January 06th, 2018|Categories: Tips and Tricks, VBA||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

Sat 30 Dec 2017

## Excel Quiz 58

By |Saturday, December 30th, 2017|Categories: Quizzes|Tags: , , , |0 Comments

## Excel Quiz 58

A quiz based on Excel functions and formulas

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