More Useful Programs - DropBox and SnagIt

Below are two other programs that I have found very useful. Again, these are my personal opinions and I have no affiliation with either company and I am NOT receiving any referral fee etc. If you have found some other useful programs let me know. I would love to try them out. 

DropBox
Another program that I have been using for about a year is Dropbox.  You can use drop box a couple of different ways.  I can take files on my computer and put a copy in the Dropbox application on my computer. Once the file has been put in the DropBox on your computer - they are then available to you on the web.
How cool is that?   Dropbox also says that it works with mobile devices such as the Blackberry however I have not yet tried that.

You can also upload files to Dropbox.com and give share privileges so that others can access files.  This is a lot more useful than emailing multiple people a file or files. You can even upload a folder.
 People with share privileges can view the files, download the file or the entire folder. It is much easier to keep track of files this way - particularly if you are updating them all the time.  Also, if the files you are sending are too big to email this is an alternative. 
I really like this program because I was getting tired of sorting through my flashdrives to find a file- of course if you don't have an Internet connection then this would not be too helpful - I learned this the hard way when on my last vacation I ended up being without internet service for a few days (always have a Plan B!).
I tend to go the low cost way when looking for programs so dropbox met that criteria since it is free if you are storing 2G or less. Of course, if you want more storage than that, they have a monthly pricing structure. If this sounds interesting to you check out http://www.dropbox.com/

SnagIt
I have used SnagIt for years. It is a screen capture program. You can capture an entire window or just a piece of it. You can add text and arrows if you want.You can then save the image as a file or to the clipboard.   You can get a 30 day free trial but you have to buy it after that. I have tried other screen capture programs and have always come back to this one so even though it costs about $50, I personally think it is worth it if you doing a lot of screen captures. http://www.techsmith.com/snagit/features/win/

Useful Software Programs

Do you like the new design of the blog? If you didn't notice, don't feel bad- it is not that different.
I have been working away on my new Excel Site called Excel-Diva.com however it is not quite ready yet. I will tell you more about it later in the week when everything on the site is actually working. There is nothing more irritating then clicking on a button that takes you nowhere.

Right now, I want to talk to you about some different software programs that I have been using. These are my personal opinions. I have not accepted any fee etc for these recommendations.  Between traveling and getting an IPAD, I have been using a lot of different software and Apps so I just throught I would share a few with you that I have found useful.

These two software programs relate to sending/uploading files.

First, if you are sending a lot of files - particularly large ones consider a zip file. The person receiving the file will certainly appreciate it. Also, a lot of mail systems now will not send large files.
I have used WinZip for years and have never had any problems with it. Winzip compresses your files and you can send a group of files or even folders in a compressed .zip format. You can also turn the zip file into an executable file so that the receiver only has to double click on it and the files all extract. You can password protect the .zip file and .exe files. You can also extract or add files into a compressed file.
So, if you have not used a zip file check it out. They offer a 30 day free trial which is always nice.
http://www.winzip.com/win/en/index.htm

The downside to the .zip and .exe file is that some firewalls will not accept them. In those cases or in cases where my .zip files is still too big for my mail system, I use YouSendIt.
I just started using this program about 2 months ago and have been very pleased with it. They have a totally free subscription and then of course they have the enhanced versions. I started out with the free subscription but I did move up to a paid subscription simply because I wanted to send multiple files at one time. You can send files to people and see when they have downloaded the files. You can upload files and share them with others. If you are interested check them out. http://www.yousendit.com/compare-plans?incid=topNavPlansSignUp_txt


That's it for now. Later, I will tell you about some cool IPAD apps.

Excel Hero Academy- Starts August 1st!

I am still tunneling through the mountains of paperwork that resulted after a 2 week vacation however I wanted to make sure and mention Daniel Ferry's Excel Hero Academy to you all.
The  Excel Hero Academy starts on August 1st so I obviously need to tell you about it now! There is not much time left to sign up.

Daniel Ferry, an Excel MVP, runs the Excel Hero Blog and the Excel Hero Academy. If you have a chance check out his blog and this course he is offering.
I will tell you that I am only recommending advanced Excel users take a look at it - that is strictly my opinion, of course.
There is no CPE currently being offered although Daniel and I are going to talk about that and see if we can offer CPE in the future on Excel Hero Academy or some portion of it.

The content, recommendations of others and testimonials from prior students is impressive and I would recommend this course anyway however I do want to let you know that I will receive a small fee if you register for the course.
Here is the list of the 12 (very in-depth, to the point of advanced) weekly modules included in the academy (remember that each module is strongly peppered with VBA):
Click here to visit excelhero.com.

Module 1: Named Formulas

Module 2: Array Formulas and Named Formulas Continued

Module 3: Charting 1

Module 4: Dashboards 1

Module 5: Database Techniques

Module 6: Advanced and Creative Formulas

Module 7: Charting 2 with Animation

Module 8: Excel and Access

Module 9: Dashboards 2

Module 10: Classes (VBA on Steroids)

Module 11: Optimization (Speeding up large/slow workbooks)

Module 12: Extreme Excel

It looks extremely interesting and I know a few of you are licking your lips over it.  I know that I am. So, check it out and even if you don't have the time to take this particular session-(there will be others), check out Daniel's blog as you may find that useful too.
Click here to visit excelhero.com.

Creating Multi-Tiered List with Data Validation and INDIRECT

I found a great tip on one of my LinkedIn Excel Discussion groups. It was how to create a multi-tiered list.

The example used was state and city.  Using a data validation in Column A to list states, the person wanted the associated cities to display in Column B.  In other words, if Cell A1 displayed Indiana then B1 should display a list of related cities. If A1 changed to Massachusetts then the list of related cities should change to reflect that.

I thought I would share this tip with you but I wanted to take it a step further so that you can see how to do this using separate sheets and not have all the data in one sheet. I also decided to do a different example so that you would see some other uses for it.

We are going to create a Product List and as different products are selected, the associated model numbers will display.

Let's go through this:

I have 2 sheets - a Summary sheet and then a Model Information sheet.
Here is the sheet named Model Info. It contains the data we are going to work with.

Here is the Summary Sheet:

Put your cursor in cell A2 and then go to the Data tab and select Data Validation.

Select List from the Allow: dropdown
Put your cursor  in the Source: dialog box and select A1:A4 on the Model Info sheet.
Click OK.






The Summary sheet now has a drop-down arrow at A2.

Click in A2 and copy that blank cell with the drop-down arrow down to A5 or A6.
Click on A2 and select the drop-down arrow and select VCR from the list we just created.

What we want to do now is set up Column B so that it will display all the models associated with whatever is displaying in Column A.

Let's go to the Model Info sheet and create a range name for all of that data.
Select A1.I14 on the Model Info sheet. 
Click on the Formulas Tab and select Create from Selection.

Make sure that there is only a checkmark in left column and click OK.
This is an easy way to create multiple range names all at once. We are telling Excel to select the Left Column which in this case are the Product Line names in Column A and create that as the range name for all of the data to the right – the model numbers. This is a lot faster than individually naming each of the ranges.

Now, let’s go back to the Summary sheet for the final steps.
Select all of  Column B (select Column header).
Select Data Validation again.

In the Allow: dialog box select List again
In the Source: dialog box, type =indirect(A1)
(make sure that it is a relative reference - not an absolute cell reference. If it an absolute, every cell will display the same contents).
 
Click OK.
 
 
 
 
Since we are referencing A1 that contains the word Product, Excel is going to give us an error message. When you see it just click Yes that we want to continue.

 
 
 
To get around the error message, you could have selected specific cells in Column B but I took the lazy way of selecting the entire column.
 
 
Click on the drop down arrow at cell B2 and you should see all the model numbers associated with the VCR product line.
Click on A3 and select another product and when you click on cell B3, you will see model numbers that are associated with that product. In my example, I selected CAM for camcorders and B3 shows all the associated model numbers.

Using Weekday() function in Fraud Audits

I just looked at the last date I blogged and realized that I am woefully behind!
I have been very busy.  I just finished writing a step-by-step EBook on Pivot Tables.
This time I wrote the EBook both for CPE credit and then created a separate EBook for people looking to purchase the EBook but who don't need CPE credit for it. Go to http://cpaselfstudy.com/category_14/Excel.htm if you are looking to purchase the CPE course (It is a 5 Hour course). If you just want to purchase the Ebook, you can find the information on that over on the top right-side of the blog under EBooks and Videos for Purchase.

In addition to that, I just finished reviewing an Excel course entitled "Excel Time Value of Money Functions for CPAs " which was written by Joe Helstrom and which can also be found at http://www.cpaselfstudy.com/.

Finally, I just finished my first 1 hour video course on Excel Tools and Tips. I am very excited about that and hope to add a few more soon.

Plus, of course it is summer and you know how that goes!

So, I am taking the lazy approach here and picking an excerpt from a course Joe and I wrote recently entitled " Fraud Audit Techniques Using Excel" that I thought you might find useful.  If you are interested in this course go to
http://cpaselfstudy.com/item_188/Fraud-Audit-Techniques-Using-Excel.htm


Using the Weekday() Function in a Fraud Audit


You can use date functions to identify potential fraud such as checking for activity on weekends.
One quick test is to see if journal entries are being processed on a weekend.

The Weekday function returns the numeric value of the day. So, for example, if you test September 1, 2010, =Weekday(A1) will return a value of 4 since 9/1/2010 was Wednesday the 4th . You could then use conditional formatting to review all the values of 6 and 7 to see if there is anything unusual about the date or you could just sort it.

• Select cell E2 and type =Weekday(A2,1)

• Press Enter

• Copy it down the column

Now that we have the weekday values, we could sort by number or we could use conditional formatting to identify Saturdays and Sundays (6 and 7)

• Select your data E2.E23

• Click on the Conditional Formatting icon on the Home Tab

• Select Highlight Cell Rules

• Select Greater Than….

• Enter 5 in the dialog box and click OK

Your data should look like the screenshot below:

You can easily see what transactions were occurring on Saturday and Sunday (6=Saturday and 7=Sunday)