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.
Category: Challenges
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…
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…
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
Challenge 67 – Find Duplicates and Show the Count
You have been given following data and you need to show corresponding count. You need to write a formula which can generate this count. If needed, you can use a maximum of helper column also. Download problem workbook from Find Duplicates and Show the Count The solution to this problem will be published after a…
Challenge 66 – Find the Position of Word
Suppose you have been given a word say "and" and you need to find which word position is this In Mr. and Mrs. Smith – Position of "and" is 2 as this is the 2nd word, the position is not 5. In Samarand Smith and Kittie Smith – the position is 3 not 2 as…
Solution – Challenge 65 – How Many Sundays on Last Date of Months
Below is a possible solution to the Challenge 65 – How Many Sundays on Last Date of Months =SUMPRODUCT((ROW(INDIRECT($B$1&":"&$B$2))=EOMONTH(ROW(INDIRECT( $B$1&":"&$B$2)),0))*(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"ddd")="Sun")) Below is an Excel sheet containing the solution Solution – Challenge 65 – How Many Sundays on Last Date of Months