FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be…
Tips & Tricks 173 – Data Validation for Case Sensitivity
There are scenarios when you want to put a data validation which is case sensitive. Hence you want USA to be entered as USA not as usa or uSA or uSA so on. You just want all capitals. Similarly, sometimes you need only small letters say maverick. Hence, not Maverick, mavericK etc.
Excel Quiz 44
Tips & Tricks 172 – Calculate Next Working day if date is of Weekend / Holiday
Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date. For example – 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or…
Downloads 20 – Excel based Consolidated Minutes of Meeting (MOM) Template
Download Link – MOM_Cumulative While earlier MOM template was or individual meetings, this MOM template can be used for all meetings held. Advantage is that you have running records of attendance, discussion and action items.
Excel Quiz 43
Tips & Tricks 171 – Convert your formulas to Absolute References
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.
Tips & Tricks 170 – Calculate Previous Working day if date is of Weekend / Holiday
Suppose you are given a date and you are asked to calculate Previous Working day if date is of weekend. If date is a regular workday, then you should show the same date. For example – 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or…
Tips & Tricks 168 – Sum Cells for a Particular Color
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 >…
Solution – Challenge 70 – Need a Data Validation Formula
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)