MrExcel in Louisville KY & Toronto

I will be in Louisville, KY this Friday, December 2nd, doing a six-hour course on Excel and Excel VBA. The event is sponsored by the Louisville Chapter of the IIA. If you are in need of CPE credits, the chapter will issue 6 credits for the course. Cost is $150 and includes the MrExcel VBA book. There are still a few seats available, so if you need some CPE credits send a note to carrie.ramsey at lgeenergy.com

Also, all of our Toronto-area readers are invited to attend the TechTV holiday meetup on Thursday, December 15, 2005. It will be held at the No Regrets Restaurant on Dufferin. Meet Call for Help personalities such as Leo Laporte, Andy Walker, Amber McArthur, Alex Lindsay and more. I will be there with autographed books and can answer you Excel questions on the spot. The event is free - I would love to see you there. Complete details are here.

Guide to Bundles in the MrExcel Store

I occassionally get a telephone call from a customer trying to figure out which bundle to buy. I just reworked many of the bundles for the Holiday 2005 season, so I took the time to build a page comparing all of the bundles. Hopefully, the comments here will help to clarify which bundle is for you.

Keep activecell in the center of the screen

During a Power Excel VBA class at the University of Akron, Carl asked: How can I keep the active cell in the center of the screen? We were working on Event Handlers at the time, and were able to build this useful bit of code:

Add this code to the worksheet event for the sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Target.Row
If x > 12 Then
ActiveWindow.ScrollRow = x - 10
End If
End Sub

Using Pivot Charts

On today's episode of Call for Help, I show how to use the charting component of Pivot Tables. Actually, I show one pivot chart and then pretty quickly jump over to creating reports with Pivot Tables and then using that data to create the chart.

This episode airs in Canada and Australia on November 16, 2005 and in the USA on Tuesday, December 20, 2005. Complete show notes are here.

Dan Bricklin's WikiCalc

This is so cool - the inventor of the spreadsheet is now testing a spreadsheet that runs in a Wiki program. This would allow you to create nicely formatted entries on webpages without having to know HTML.

I was tipped off to the program by a posting at ZDNet:

Bricklin's WikiCalc: Much much more than just a mashup of wikis and spreadsheets.

But, go to Dan's website to actually try it out.

Recognition Program for Top Sales Reps

On today's episode of Call For Help, I discuss a spreadsheet application for recognizing your top sales reps. This could be used daily in a retail store or a car dealership or anywhere with a sales team.

The Excel-Know-How in this tip is how to combine the RANK() function with the VLOOKUP() function to perform a sort using only formulas. This allows you to basically point your formulas to an external copy of an existing sales report and to turn it into a beautiful looking report that you can post in the break room.

See the complete details here.

Pivot Tables from more than 65,536 Rows

Overcome Excel's 65,536 row limit by creating an Excel pivot table from Access data. This episode of Call for Help which premieres tonight on TechTV in Canada and Australia will give you the complete details.

If you are a pivot table guru, you probably click "Next>" through the first two steps of the PivotTable wizard. This technique shows how to create a pivot table from 85,000 rows of data stored in Access.

Creating Pivot Tables from Access Data

Overcome Excel's 65,536 row limit! Build your pivot tables from data stored in Access. Show notes from Call for Help episode 274 will show you how.