Translate

Showing posts with label Advance Excel. Show all posts
Showing posts with label Advance Excel. Show all posts

Sunday, April 30, 2017

Variable Drop Down Menu / Data Validation (DV) List



Objective
In a model you need multiple drop down menus, the choice in drop down menus is dependent upon each other. Like if ones chooses Asia (in 1st menu), then only Asian Countries be available as second choice (2nd menu); for Europe only European countries be prompted.

Thursday, April 13, 2017

6 Ways: Unique List of items from Multiple entries



Objective
You have some data where a particular field has many items occurring repeatedly and you need a list of unique items only like from (A,B,A,C,D,E,F,F,E,C,B,A,D) you need (A,B,C,D,E)


Monday, April 3, 2017

Array (Excel Function - Conceptual)


Objective
Array is a very powerful tool that can enhance power of normal excel functions manifold. Arrays are also used in VBA to store and retrieve data.

Most of the times, my audience is non-IT people, who are not familiar with Array concept. So here it is in layman language.

Tuesday, March 21, 2017

Key Advanced Skill: Creating List from scattered entries by Avoiding/Ignoring/Skipping empty rows



Objective
You are working on a data set and want to get your results based upon certain criteria.  Now the problem is your result contains blank rows / rows which do not qualify with your criteria. You want result as a list i.e. without blank rows. 

This model/method resolve many data picking/filtration issues,therefore in my opinion its a KEY concept.

Tuesday, March 14, 2017

VLOOKUP Search in middle or right column & get result from left side column(s)



Objective
VLOOKUP always search in left column and get result from right side. How to make VLOOKUP search in right column and get result from left column.

Wednesday, March 8, 2017

Monday, February 27, 2017

VBA Basics / My First Steps to Programming (Lesson 1)


Objective
Understand what is Visual Basic, how it is embedded in Office applications, what it can do and basic idea of using it.


Monday, February 20, 2017

VLOOKUP Multiple Occurrence


Objective
Many a times, while searching a list, the searched text/value may appear more than once. Standard VLOOKUP will return first occurrence from top. The question is how to find second, third or fourth occurrence?


Friday, February 17, 2017

How to Show Progress Bar in Excel / Macro


Objective
Like modern software, while your Macro/code is rendering a time consuming service, a percentage completion status should be shown to the user.