Article 15 – Dependent Data List 01 – Creating Flexible / Dynamic Dependent Data List
—- Excel file related to this article can be downloaded from Dependent Drop Down List —- In this article, I want to create a dynamic dependent data list. It means that entries can be added, removed at any point of time without impacting your Dependent Data List. Without making matter complicated, this article assumes that first…
Tips & Tricks 95 – Send Excel Workbook / Worksheet as Attachment
You can very easily send an Excel / Workbook as an attachment directly from Excel. To send Excel Workbook To use this functionality, your workbook need not be saved. When you use a step from below, it will attach that point of time copy of Excel workbook in the mail. File > Share > E…
Tips & Tricks 94 – Select Big Range Very Easily
You have a need to select A1 to A10. This is a very easy job to do. Suppose, you need to select A1 to A10000. Now, it becomes more cumbersome. Suppose you need to select E23 to AB4325. It is all the more cumbersome job for you. Approach 1 – Put the range in Name…
Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum
The Excel file related to this problem can be downloaded from Challenge – Most Consecutive Appearance Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and…
Excel Quiz 13
Challenge 16 – Team Pairing
Given the team list below, the challenge before you is to align teams RANDOMLY under Group A and Group B where one team faces another and there is no repetitions of the team. The formula should be flexible, in case, team list increases / decreases. The team list will always be even as if number…
Tips & Tricks 93 – Sorting from Left to Right
Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel. 1. Select your data in the row. 2. Data tab> Sort OR Home tab > Sort & Filter OR ALT+DS…
Challenge 15 – Floyd's Triangle
Wikipedia article related to Floyd's Triangle will give you details about this. But, one look at below and you will understand what is this. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 18 Now, the challenge…
Tips & Tricks 92 – Financial Function – Calculate Effective Interest
You are applying for a loan and an interest rate has been quoted. The interest rate which is quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally pay EMIs every month. They simply…