Outlook Validation for Forms and Zip codes [SOLVED]

Outlook Validation for Forms

We had a client where they had a very specific validation that needed to be applied to their contacts. The Zipcode field needed to start with a country Letter. Here is the validation necessary.

Watch the video

([Business Address Country/Region] =”Austria” AND Left( [Business Address Postal Code] , 2 ) = “A-“) or ([Business Address Country/Region] =”Germany” AND Left( [Business Address Postal Code] , 2 ) = “D-“)  or ([Business Address Country/Region] =”Switzerland” AND Left( [Business Address Postal Code] , 3 ) = “CH-“)  or ([Business Address Country/Region] =”Italy” AND Left( [Business Address Postal Code] , 2 ) = “I-“) or ([Business Address Country/Region] =”Ireleland” AND Left( [Business Address Postal Code] , 3 ) = “IE-“)  or ([Business Address Country/Region] =”Iceland” AND Left( [Business Address Postal Code] , 3 ) = “IS-“)  or ([Business Address Country/Region] =”Netherlands” AND Left( [Business Address Postal Code] , 3 ) = “NL-“)  or ([Business Address Country/Region] =”France” AND Left( [Business Address Postal Code] , 2 ) = “F-“) or ([Business Address Country/Region] =”Spain” AND Left( [Business Address Postal Code] , 2 ) = “E-“) or ([Business Address Country/Region] =”Finland” AND Left( [Business Address Postal Code] , 3 ) = “FI-“) or ([Business Address Country/Region] =”Norway” AND Left( [Business Address Postal Code] , 2 ) = “N-“) or ([Business Address Country/Region] =”Denmark” AND Left( [Business Address Postal Code] , 3 ) = “DK-“) or ([Business Address Country/Region] =”Belgium” AND Left( [Business Address Postal Code] , 2 ) = “B-“) or ([Business Address Country/Region] =”Luxemburg” AND Left( [Business Address Postal Code] , 2 ) = “L-“) or ([Business Address Country/Region] =”Poland” AND Left( [Business Address Postal Code] , 2 ) = “PL-“) or ([Business Address Country/Region] =”Portugal” AND Left( [Business Address Postal Code] , 2 ) = “P-“)

 

Outlook Validation for Forms

How to Set default formatting Outlook 2010 contacts

How to Set default formatting Outlook 2010 contacts

Change the default Country/Region for addresses

Outlook gets the default country for contact addresses from the Region applet in Windows 8. In Windows 7 this is called Region and Language Options, and in Windows XP, it’s Regional and Language.

Check Address dialog

Open the Control Panel, find Region. If you use the Category view, it’s under Clock, Language, and Region, or use the Search field.

Select the desired country from the dropdown on the Format tab. In most cases, the change will take effect immediately, however, you will need to close and re-open contacts that are open.

Change the format on the Region dialog

Choose the correct Country or Region for your location in the Format dialog. Don’t choose the Country based on the date and number formats you prefer, those can be changed by clicking Additional Settings. See How to change the Calendar date format in Microsoft Outlook for instructions.

Change the Country/Region for phone numbers

The contact’s phone number field uses the Phone and Modem Location. In most cases, you will use the same country in Region options and as the Location in Phone and Modem Options, but if you want to use a different default Country for phone numbers, you’ll make the change in the Phone and Modem options dialog.

Check Phone Number

To change the phone location, open the Control Panel and find Phone and Modem. If you use the Category view, use Search to find it. Double-click to open Phone and Modem applet.
Dialing location dialog

If you are changing the current location, double-click on it or select and click Edit. Select the desired Country or Region from list on the General tab. If you need multiple locations (most people need just one location), Outlook uses the location that is currently selected as the default when adding phone numbers.

Edit location dialog box

How to Set default formatting Outlook 2010 contacts

Importing ADP Payroll into Quickbooks [SOLVED]

Importing ADP Payroll into Quickbooks

ADP is a payroll service that takes care of paying your payroll expenses on your behalf, both to your employees and to government agencies. ADP allows you to export your payroll data using its general ledger interface. This data can then be imported into QuickBooks, allowing you to include all the data as part of your financial statements.

Exporting from ADP

To add the data to QuickBooks, export it from ADP’s general ledger interface. Click “Define G/L File” and select “Use a Predefined G/L File for Map Assistant.” Then choose QuickBooks from the drop-down menu. You are then prompted to “map” the payroll items; you must identify the corresponding general ledger account name or number for each payroll item. This allows you to seamlessly integrate your payroll data into the general ledger. You can reuse the map each time you export data to QuickBooks. Select the desired pay period and click “Download” to export the data to your computer.

Importing to QuickBooks

After you export the data from ADP, adding the data to your QuickBooks general ledger is a simple process. In QuickBooks, click the “File” menu and select “Import.” Then choose the file that you have just saved to your computer. The data is automatically added to your general ledger, integrating with the pre-existing general ledger accounts. Now you can create any number of financial reports, such as balance sheets and income statements.

Quickbooks Logo

Importing ADP Payroll into Quickbooks

How to Multiple PivotTables with Different Month and Day Groupings from the same Data Source [ANSWER]

How to create Multiple PivotTables with Different Month and Day Groupings from the same Data Source

When multiple Pivot Tables are created it initiates the OnLine Analytical Processing by default. Basically this translates to a smaller file size and caching. There is a way to create Pivot Tables from the same source but not have the Analytical Processing on.

The problem is when you need to have a field grouped one way (For example in Months, and a different PivotTable grouped in Days) it is not possible.

Two separate PivotTables may be created, in Excel 2013 for example, with separate caches driven from the same raw data with use of the PivotTable and PivotChart Wizard (Alt+D, P). The second (and any subsequent) time around, before finishing “Step 2 of 3” click Next and the following message box should appear:

SO25731981 example

Be sure to select No and be warned that this may significantly increase the size of your file.

 

The other option is to create grouping columns in your raw data. For example:

=TEXT(B4,”mmm”)
This will turn a date into a short Month Name. Ex. Jan

=TEXT(B4,”mmmm”)
This will turn a date into a long Month Name. Ex. January

 

Multiple PivotTables with Different Month and Day Groupings from the same Data Source

Excel VBA Macro to Close a Workbook without Saving and Reopen

Excel VBA Macro to Close a Workbook without Saving and Reopen

I wrote this macro and use it all the time when writing new macros. Feel free to use.

 

Sub ClosewithoutsaveAndReopen()
‘Defines Variables
Dim nameOfCurrentWorkbook As Variant
Dim nameOfCurrentWorkbookPath As Variant

‘Finds the name of the current workbook
nameOfCurrentWorkbook = ActiveWorkbook.Name

‘finds the current path of hte workbook
nameOfCurrentWorkbookPath = ActiveWorkbook.Path

ActiveWorkbook.Close False
‘Reopens the same worbook you just closed
‘If in the same workbook
Workbooks.Open (nameOfCurrentWorkbookPath & “\” & nameOfCurrentWorkbook)
‘if in a specific workbook
‘Workbooks.Open (“C:\Users\VMWindows7\Desktop\SAS CARB 2012_44.xlsx”)
End Sub

 

 

Excel VBA Macro to Close a Workbook without Saving and Reopen

The Clipboard cannot be emptied. Another program might be using the Clipboard [ANSWERED]

The Problem is Skype Click to Call Button.

cliboarded empty

Skype Click to Call add-on that is installed in your browser when you install Skype. Skype released a new version of this add-on back in April that resolves this issue with Excel.

The following sticky goes into more detail about how to get the latest version of Skype’s Click to Call:

Text loses formatting when I use Cut, Copy, Paste, Paste Special or Format Painter in Excel

Automatically reduce the size of pictures included with an e-mail message [Solved]

Automatically reduce the size of pictures included with an e-mail message

Outlook LogoThere are two ways to select pictures to automatically reduce them in size and include them as an attachment to an e-mail message — in Microsoft Office Outlook 2007, or from Windows Explorer.

 Note   Your original picture will not be modified. Only the copy of the picture being sent will be reduced in size.

In Outlook

  1. Create a new e-mail message in Outlook.
  2. On the Insert tab, in the Include group, click Attach File.

 Tip   You can also drag and drop a picture from Windows Explorer. The picture file will be attached to the e-mail message.

  1. On the Insert tab, click the Include Dialog Box Launcher Button image.
  2. In the Attachment Options pane, under Picture options, in the Select picture size drop-down list, click the size of the picture you want to include.

Attachment Options pane

 Note   If you embed the picture in the body of the message by using the Picture command in the Illustrations group, the automatic picture resize feature is not available.

  1. When you are finished composing your e-mail message, click Send.

In Windows Explorer

  1. Open Windows Explorer by clicking Start, and then double-clicking Computer.

 Note   Double-click My Computer in Windows XP.

Keyboard shortcut  To open Windows Explorer, press Windows logo key+E.

  1. Navigate to the folder that contains the pictures you want to send.
  2. Select a picture. To select multiple files, press and hold CTRL as you click each file.
  3. Right-click a selected file, point to Send To, and then click Mail Recipient.

The Send Pictures via E-mail dialog box appears.

  1. Click Make all my pictures smaller, and then click OK.

Send Pictures bia E-mail dialog box

 Note   To specify the exact size to make the picture, click Show more options, and then click the size you want.

A new Outlook message window appears with the attached picture.

  1. Enter the recipient information in the To, Cc, and Bcc boxes.
  2. If you want, change the subject information in the Subject box.
  3. If you want, change or enter any information you want to include in the message body.
  4. Click Send.

Credit: http://office.microsoft.com/en-us/outlook-help/reduce-the-size-of-pictures-and-attachments-HA010236594.aspx

How to Freeze Panes in Excel [Solved]

How to Freeze Panes in Excel

Excel LogoIf you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

 

Freeze Top Row

To freeze the top row, execute the following steps.

1. On the View tab, click Freeze Panes, Freeze Top Row.

Freeze Top Row

2. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.

Freeze Top Row Result

Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.

1. On the View tab, click Freeze Panes, Unfreeze Panes.

Unfreeze Panes

Freeze Panes

To freeze panes, execute the following steps.

1. Select row 3.

2. On the View tab, click Freeze Panes, Freeze Panes.

Freeze Rows

3. Scroll down to the rest of the worksheet.

Result. All rows above row 3 are frozen.

Freeze Rows Result

Note: to keep columns visible while scrolling to the right of the worksheet, select a column and click Freeze panes.

4. Select cell C3 (unfreeze panes first).

5. On the View tab, click Freeze Panes, Freeze Panes.

Result. The region above row 3 and to the left of column C is frozen.

Freeze Rows and Columns Result

Credit: http://www.excel-easy.com/examples/freeze-panes.html

How to Merge Cells in Excel

How to Merge Cells in Excel

Excel LogoThis document explains how to merge cells within all versions of Microsoft Excel.

Merging cells is often used when a title is to be centered over a particular section of a spreadsheet. When a group of cells is merged, only the text in the upper-leftmost box is preserved.

To merge a group of cells:

  1. Highlight or select a range of cells.
  2. Right-click on the highlighted cells and select Format Cells….
  3. Click the Alignment tab and place a checkmark in the checkbox labeled Merge cells.

To merge a group of cells and center the text, you can also use the Merge and Center button on the Excel tool bar. Again, this will only preserve the text in the upper-leftmost cell.

  1. Highlight or select a range of cells.

Click the Merge and Center button on the toolbar.

Excel XP and Excel 2003
Excel XP and 2003 Merge and Center button

Excel 2007
Excel 2007 Merge and Center button

Excel 2008 (Mac)

Excel 2008 Merge and Center button

Excel 2010

Excel 2010 Merge and Center button

Excel 2011 (Mac)

Excel 2011 Mac Merge and Center button

Excel 2013
Excel 2013 Merge and Center button

Credit: https://kb.wisc.edu/helpdesk/page.php?id=939