Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.
The file related to this article can be downloaded from Dynamic Pivot Tables
We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.
Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers
Put following formula B14 and drag right and down
Download the workbook related to this challenge - Sum for Merged Cells
This time challenge is to produce the sum where header is merged cells. For the layout like below -
You need to write a single formula which can be dragged right and down to generate the sum for below table
The solution to this challenge will be published after a month i.e. on 13-Jun-17.
Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted
One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be firstname.lastname@example.org as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.
Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.
Following is the methodology to test -
1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.
Excel Quiz 28 - LOOKUPs - Part II
0 of 10 questions completed
Part II quiz on LOOKUPs in Excel
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
0 of 10 questions answered correctly
Time has elapsed
You have reached 0 of 0 points, (0)
Question 1 of 10
Question 2 of 10
Question 3 of 10
Question 4 of 10
What would be the answer for following -
Question 5 of 10
Question 6 of 10
Question 7 of 10
What would be the answer for following -
Question 8 of 10
Question 9 of 10
Question 10 of 10
Below is a possible solution to the Challenge 24 – Sum a Range Conditionally where Range Inputs are variables
Put following formula for the result -
The workbook illustrating the solution can be downloaded from Solution - Challenge 24 – Sum a Range Conditionally where Range Inputs are variables
--- Excel related to this challenge can be downloaded from Challenge - Sum a Range Conditionally ---
Suppose, you have values in column A and D1 and D2 contains the range references. D3 contains the condition value for greater than. In this example, you will need to sum up between A4 and A8 where values are > 30.
Of course, A4, A8 and 30 are variables and driven by values in D1, D2 and D3.
You may post your answers in comments section.
Note - Solution to this challenge will be published after 1 month i.e. on 28-Sep-15.
Article 16 – Dependent Data List 02 – Creating Flexible / Dynamic Dependent Data List even First Drop Down is Flexible
--- The Excel file related to this article can be downloaded from Dependent Drop Down List 02 ---
In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as well as horizontally.
1. Prepare the formula for first drop down.
In article 15, we saw that it is very simple i.e. $F$1:$K$1. Let's assume that maximum it can grow till Z1. In this case, we need to prepare a formula which will give us a range of $F$1:$K$1 within the range of $F$1:$Z$1 if South America is selected. If another entry is added or deleted, it should adjust appropriately.
The formula in this case would be -
(Though OFFSET is a volatile formula but simplicity of OFFSET is a reason to use)
2. Prepare the formula for second drop down
The formula was already there, only change is that $K is replaced by $Z. (As I said earlier 12 is a number till which the list can grow. You can put any number say 100, 900 etc.)
3. Create the first drop down list and second drop down list utilizing above formulas.
Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
Now, your dynamic drop down list which can grow vertically as well as horizontally is ready.