VBA - Create an 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.
Sat 25
Nov 2017
Sat 18
Nov 2017
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 add 1 to A1
=TEXT(A1+1&"Jan2017","dddd")
Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1
=TEXT(A1+5&"Jan2017","dddd")
Tue 14
Nov 2017
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
Sat 11
Nov 2017
VBA - Function to Validate IP Addresss
Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255
You can put following type of construct in a cell
=IsValidIP(A2)
Thu 09
Nov 2017
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 month i.e. on 9-Dec-17.
Sat 04
Nov 2017
Tips & Tricks 165 - Convert Weekday Names to Numbers
Suppose Cell A2 contains weekday names like Sunday, Monday.....(or Sun, Mon...), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7.
=ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0)
=MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)
If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1 and Sun = 7
=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)
=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)