ANmar
We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”) I added to prevent • Read More »
ANmar
Convert one long column into two This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column This was the answer for this question Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB Show two • Read More »
ANmar
It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it Mutliple-dual-validations that depend on each other. Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the • Read More »
ANmar
Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas Can also be used as form to read from a large table in another sheet After creating two names, one is “Months” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1) And the other one is “Month_All” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3) Draw a listbox using “Developer • Read More »
ANmar
Creating a Data Validation based on another Data Validation Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it. You need to create two names (Formula > Name Manager), one has this formula =OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2)) The other one has this