Tuesday, July 11, 2006

Restricting Scrolling in an Excel Worksheet

Doug Klippert fom Unofficial Microsoft Office Stuff writes a really useful post about how to use the scroll property to restrict scrolling in a worksheet without using protection.

You can protect a spreadsheet so that data is shielded from inadvertent entries.
There is another way to set up a scroll area that does not involve protection.
Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
To cancel the Scroll Area restricted range, clear the Scroll Area text box.

Pictures and how to find it in Excel 2007 can be found in his site.

Wednesday, July 05, 2006

Excel news and other links

This is just a list of a bunch of websites with interesting Excel related reading.

From Unofficial Micorosft Office Stuff
Access - Excel - HTML - XML XML makes data transferable between applications. Here is a tutorial with downloadable files.
Excel - 2003- VBA Help File

Here is a downloadable compiled Help file covering the vagaries of Excel VBA. You can use this file without having to, necessarily opening the application. Excel 2003 VBA Language Reference
Lists in 2003 - Tabled in 2007
Split Large Text Files Between Worksheets. The vba code to process text files with more than 65536 rows.

From J-Walk
Excel 2007 Command Guide - maybe the solution to my problem with locating commands.
Excel 2003 to Excel 2007 command reference guide.
Wondering where your favorite Excel 2003 commands are located in the new Excel 2007 interface? Or just want to explore the rich, new design with a little guidance?
You're in the right place. We've developed a visual, interactive reference guide to help you quickly learn where things are. Note - only works if you are running the Beta Office 2007 application.
Joel On Excel Interesting reading for Excel users. At Joel on Software: My First BillG Review.
Office 2007 Delayed Again Microsoft said it will now aim for a launch of Office 2007 to business customers by the end of 2006 rather than an earlier target of October. Microsoft also said it would delay the general availability of the Office upgrade to early 2007 from its previous January target.

From Andrew's Excel Tips
Rounded Comments and New Book Navigator Andrew has created some free Addins that are pretty useful. Try them out.

Office 2007 Excel Data Auto Filter

I have been playing with the new Office 2007. It is still a beta so some of these things may change. However I will start writing posts now on the differences and ways it is going to affect people as they change over to it.

My initial review of the product is that for Excel and Word users it is going to be more painful than good. While the new UI is flash and a fresh new look, they have moved stuff!
My first problem came when I attempted to add a simple Auto Data Filter to a table in Excel. It was not where it used to be and the keyboard short cuts did not work. As I don't have Offce 2007 in front of me I can't tell you where it can be found right now, but I will do that later. When I did find it the actual use of the filter has changed. It is now more awkward to filter by a found result.

Suffice to say, I think this is going to be the first post of many lamenting the changes for no apparent extra functionality.