CHRISdotTODD

When I get data from the screen view in Salesforce.com, the results have an empty row between each row of data. With a little VBA help to create a macro, it's easy to clean it up. Here's how to do this: Go to Tools >> Macros >> Visual Basic Editor OR press Alt-F11. The Project Explorer should be open in the upper-left corner. If not press Ctrl-R. In the Project Explorer, double-click the worksheet name to add the macro only to a single worksheet. Double-click ThisWorkbook to have it available to all worksheets. Paste the code (option 1 or option 2) into the worksheet. Go to View >> Microsoft Excel OR press Alt-F11. Select column A. Go to Tools >> Macros >> Macros OR press Alt-F8. Highlight the DeleteRows macro and click Run. Wait as it process through each row. To make it faster, only highlight the rows you want checked. OPTION 1 Sub DeleteRows() RowCount = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = F

Excel provides an easy formula to set text values in cells to the correct or proper case. This is very helpful when cleaning up data before entering into a database. If the source was from a third-party -- perhaps from user registrations, it's not unusual to have users not capitalize proper names. Here's an example: You want st. paul to read St. Paul You want project manager to read Project Manager Here's the formula: =PROPER(A1) where A1 is the cell of the value you want to convert If you are going to reference the value later, remember you can use the Text command too. =TEXT(PROPER(A1),"#") If you want to convert the entire value to upper case, use the UPPER function. =UPPER(A1)

I recently noticed a new problem with Word and Excel opening rather slow -- on a new laptop no less. And when I was connecting through our VPN to the office, I would get an error message, "The printer has not yet responded... Continue to wait?" Turns out there was a problem with my default printer definition in Windows being corrupt. I delete the old definition and created a new definition (Add Printers and Faxes) and the problem has gone away.

In Excel, I will use formulas quite frequently for my analysis. At times I want to reference the resulting value of the formula for comparison or to feed another formula. Unfortunately, if you are doing a comparison, Excel will try to compare the formula instead of the formulas results. Well, turns out there is an easy way to correct this. If you want your formula result to be a number, then use the VALUE function. If you want the formula result to be text, use the TEXT function. Here is the syntax for each: VALUE(text) TEXT(value,format_text) -- The format_text value can be any format from the Category box on the Number tab (in the Format Cells dialog box) except General. Here is an example of each: =VALUE(MID(A2,46,FIND("&",A2)-46)) -- this actually finds a number beginning with the 46th character of a string, and continues until it finds the ampersand character. By wrapping the formula with VALUE, I can now compare the number to other numbers in my looku

I just got a new laptop and was reminded of a common annoyance in MS Office products. The menus never fully display immediately -- you either have to click the arrow at the bottom of the menu or wait a few seconds. There is actually a fairly easy way to keep them expanded all the time. Right-click any toolbar Select Customize Check "Always show full menus" That's it.

I recently started having a problem where my Excel 2003 and Word 2003 files would not open when clicked, unless Excel and/or Word was already open. If the application wasn't open, it would open the application, but not load the file. I did quite a bit of searching, but couldn't seem to find the right answer. Some sites recommended checking a setting to Ignore other applications -- a setting that wasn't even available in Word. Turns out for both applications, it was the Addins that were causing troubles. To remove the offending Addin, you need to edit the Registry. Here's the process I used. Go to Start > Run... , enter regedit, and click OK . Go to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Word\ Addins . Right-click on Addins and select Export . This will let you re-import the keys. Delete the individual Addins one at a time, testing each time to find the offending Addin. Once you identify the offending Addin, re-import all Addin keys, and de

Formula explanation clarified 22-May-08. I recently had the need to compare a long list of values with another list, and for the matches get a value from the same row. For example, if you have a long list of phone numbers and you want to find who they belong to, you could compare the phone numbers to a contact list, and for every match pull out the name. Oh, and if there is no match, you don't want to get an error message, but rather something more intelligent. It takes 4 Excel functions to do this: IF (logical_test,value_if_true,value_if_false) ISERROR : Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) INDEX (array,row_num,column_num) MATCH (lookup_value,lookup_array,match_type) Here's how to do it: =IF(ISERROR(INDEX(phone!$B$1:$B$25, MATCH(A8,phone!$A$1:$A$25,0))),"NA", INDEX(phone!$B$1:$B$25,MATCH(A8,phone!$A$1:$A$25,0))) (I used carriage returns for display, this should be one continuous string) phone!$A$1:$A$25 refers to

I was working through some what-if analysis in Excel, and I wanted a simple way to adjust some values within my calculations based on input from my colleagues. I did my typical spreadsheet creation, section for input values and sections for outputs, and a graph of the output. Now I wanted my graph to update whenever I changed the input values. I could easily type in a new value, but that quickly got tedious. Using Data Validation , I was able to create drop-downs for the inputs that frequently might change. Here's how to do it. Create your worksheet, with a section that lists your input values In a new column, list the values you want in the drop-down (it must be on the same worksheet) Put your cursor in the field where you want the drop-down and go to Data | Validation... On the Settings tab, select List from the list labeled Allow Also on the Settings tab, click the table icon next to the Source field and highlight your list (step 2) Press Enter to accept Click OK With the n

I found a clever Excel template on the Microsoft site . With this template, it can help you solve Soduko puzzles. You enter the numbers you have, and have it calculate possible answers for the remaining open spots. If it cannot completely solve it, try 1 of the possible numbers and recalculate. Eventually, you will solve the puzzle. Microsoft has other game templates as well.

Excel is a great program to manipulate your data, perform what-ifs, and graphically display results. But it's not straight forward when you want to know the which Quarter certain activity took place. Fortunately, with a simple formula you can determine the Quarter. =ROUNDUP(MONTH(A1)/3,0) Roundup can be a useful function in other applications as well. The last parameter (0 in this example) is used to change the rounding level. A positive number will add decimal places, while a negative number will round left of the decimal (i.e. -1 rounds to 10s and -2 rounds to 100s).

Did you know that you can easily load data from a website into Excel? You can even do it with a Macro for sites you visit often. For example if you were tracking your investments or you were monitoring the stats for fantasy basketball. Collecting the data is 7 easy steps. Select Data from the menu Select Import External Data Select New Web Query... Enter the URL of the page that contains the data to import Excel will recognize tables. Select the table(s) that contains the data you want to import Select the Import button Confirm the first cell of where the data should be inserted That's all there is to it. If you want to create a Macro, use the Macro Recorder (Tools menu) and repeat the steps. You can then add additional code if you want to import different sites into different sheets. Happy data collecting!

I found an old trick in Excel the other day... if you highlight cells, the sum will appear in the status bar below. This works whether the cells are horizontal, vertical, or in a block. You can also select cells to sum from various parts of your spreadsheet by holding the Ctrl key down as you select each.

I have been working with an Excel file for about 18 months -- each month I link to a monthly Excel data file. This gives me data in one file that I can use to trend many different segments. I am not sure when, but this file had ballooned to over 3 MBs. Finally this month when I tried to upload it to our intranet, I received a file size limit exceeded message. I searched and searched, and could not find any thing that would compress or clean up the file. Finally I went to Google and found a solution. The credit goes to OzGrid Business Applications . Here is a link to the entire article , and below is what I had done to reduce the file size. The first thing of course is to make a backup... just in case I then performed the following on each worksheet (only 2 in my case): Select a cell and press F5 Click the Special... button Click the Blanks radio button Click OK Select Edit | Clear | All Manually go to the last row in the worksheet (do not use Edit menu shortcut) Select the entire r

Many of us have used Excel for things where a better tool is available, such as using it for a database. We have also not done a good job in learning about all its powerful functions, nor how to leverage its powerful programming language. Well, I have found a site that has used Excel in a non-traditional way, but surely in this effort, learned how to use the powerful programming language. Of course I am talking about using Excel to play Pacman . I cannot read Japanese, so I cannot give you any more details, but this is truly a novel approach. Have fun!