tag:blogger.com,1999:blog-11238774.post5733378839902990296..comments2024-03-11T05:16:11.252-07:00Comments on Excel_Geek...I'll Do That in Excel for $50: I found another Excel geek at DreamforceExcel_Geekhttp://www.blogger.com/profile/15299063299312360136noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-11238774.post-16398991904272165162007-09-19T17:13:00.000-07:002007-09-19T17:13:00.000-07:00A pleasure meeting you too, Eric!Two random though...A pleasure meeting you too, Eric!<BR/><BR/>Two random thoughts on reading the original "Filling in the blanks..." post:<BR/><BR/>1) I'm a fan of the radical reduction of IF tests. Any time I see an IF test that returns either a True or False, I think, "Is that IF necessary?"<BR/><BR/>Frinstance, in your code, you have <BR/><BR/>If Left(CEL_REF.Formula, 1) = "=" Then<BR/> ISFORMULA = True<BR/>Else<BR/> ISFORMULA = False<BR/>End If<BR/><BR/>This can be refactored into a single line as<BR/><BR/>ISFORMULA = (Left(CEL_REF.Formula, 1) = "=")<BR/><BR/>It takes a little getting used to, but code typed using the latter style (a) runs faster and (b) has more geek cred :-)<BR/><BR/>This principle also applies to the IF spreadsheet function. For example, if you wanted to test whether a certain cell equals five, you might type<BR/><BR/>=IF(A1=5, True, False)<BR/><BR/>That formula works exactly the same as<BR/><BR/>=A1=5<BR/><BR/>but the latter formula will run faster, which is handy if you have thousands of rows.<BR/><BR/>2) Excel users love love love to draw attention to specific rows or cells by highlighting or coloring them. While I have no problem with colored rows/cells per se, it can be hard to find all the colored rows/cells in a big XLS. Whenever I'm asked to color specific rows/cells in a big doc, I also create a new column and populate the appropriate rows with a value ("Error row", "outlier", whatever) so that the dataset can be easily sorted later.<BR/><BR/>Happy data-crunching!Ezrahttps://www.blogger.com/profile/17259395111040355996noreply@blogger.com