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)
Challenge 70 – Need a Data Validation Formula
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 MM/YYYY for the year 2018 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.
Excel Quiz 42
Downloads 19 – Excel based Minutes of Meeting Template
Download link – MOM_Individual This is a Minutes of Meeting (MOM) template which can be used for individual meetings. Most of the MOM templates are based on Word format whereas this is based on Excel template.
Tips & Tricks 169 – Get the Source of a Pivot Table
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…
Excel Quiz 41
Solution – Challenge 69- Need Help in Not Showing 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…