NYIM – Deal of the Day – August 23!

Our PowerPoint for Business Class on August 25, 2016 is on sale for $149! (reduced from $199)

We offer the most practical PowerPoint group classes and private training in NYC.

The PowerPoint class concentrates on the most common topics that we see NYC businesses using on a daily basis.

Visit us at www.training-nyc.com or call 212-658-1918 to enroll while the price is hot!

NYIM – Deal of the Day!

Our Keynote Group Class on August 23, 2016 is on sale for $149!  (reduced from $199)

We offer the most practical Keynote group classes and private training in NYC.

Learn how to create beautiful and engaging presentations with Apple’s Keynote software. Understand how to collaborate with PowerPoint users while maintaining the ease of use.

Visit us at www.training-nyc.com or call 212-658-1918 to enroll while the price is hot!

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