Challenge 69 – Need Help in Not Showing Comments
Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these. The workbook can be downloaded from Challenge 69 – Comments The solution to above challenge will be published after a month i.e. on 20-Feb-2018.
Solution – Challenge 68 – Generate a Particular Sequence
Below is a possible solution to the problem Challenge 67 – Generate a Particular Sequence – This consists of 2 logic. Generate Triangular number. The formula for which is n*(n+1)/2 Generate Column Labels (A, B, AA, ZZ….) from numbers, the formula for which is =SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to…
Challenge 68 – Generate a Particular Sequence
Study the sequence below and you need to write a formula which when dragged down should generate the below sequence. For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only. You need to generate upto 100 entries. The solution to this challenge will be…
Solution – Challenge 67 – Find Duplicates and Show the Count
Below is a possible solution to the problem Challenge 67 – Find Duplicates and Show the Count Use a helper column in which put following formula – =LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")), " ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "), SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1) In the last column, put following formula – =IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"") The solution work can be downloaded from Solution – Find Duplicates and…
Tips & Tricks 167 – Shortcut for Refresh All Pivot Tables Not Working
To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task. But on most of the computer this shortcut may not work. The reason is most…
Excel Quiz 39
VBA – Create a Clickable Index (Summary, Table of Contents) Sheet Macro
Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.
Tips & Tricks 166 – Convert a Number to Weekday Name
Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday =TEXT(A1&"Jan2017","dddd") To show only 3 characters of the Weekday Name =TEXT(A1&"Jan2017","ddd") You can add a number to A1 if you want to show some other Weekday Name Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just…
Solution – Challenge 66 – Find the Position of Word
Below is a proposed solution for the Challenge 66 – Find the Position of Word Put the following formula and drag down. Let's assume the word "and" in cell D2 – =LEN(REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),""))-LEN(SUBSTITUTE( REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),"")," ",""))+1 You may download the solution workbook – Solution – Challenge 66 – Find the Position of Word