Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Default sorting options in Excel

This article has been written in response to a readers question.

CB107982_LoRes Traci writes:

When I take a spreadsheet that someone else has created & remove some unwanted columns & rows, add a notes column of my own outside of the last column of their original spreadsheet, it won’t pick up that last “notes” column when it sorts.  If I highlight the entire document it will, but most people don’t highlight the whole thing when they sort.  We share this document with other people, they sort it and don’t realize that they didn’t sort the notes field.  Do you know of a fix to include the extra added “notes” column in the “my data has headers” “sort by” option that will travel with the document?

jethro's picture

Excel News from Charley Kyd

Introducing IncSight DB

Charley Kyd has extended his offerings for business excel users with a new product called IncSight DB.

Unlike his first plug-n-play dashboard product, this one links its reports to an Excel database. This design offers several great advantages:

  • It should take less than an hour to set up a report.
  • Reports can be updated in seconds each period.
  • The Excel database can contain formulas that return data from PivotTables, text files, OLAP databases, and other sources.

You can see a video of IncSight DB at his site, which shows how he sets up a new dashboard in about 20 minutes.

Charley is offering a $10 coupon for all orders of $50 or more. But the coupon will be good only for the rest of July. So if you're thinking about ordering, now's the time.

Coupon code: July 2009

When you order IncSight DB, enter this code in the payment form and choose Apply. After you do so, you'll see the discount amount subtracted from your order.

Charley also writes about Excel 2010

jethro's picture

Excel Links for today

Dick Kusleika writes:

The Excel object model provides the Application.CalculationInterruptKey property to control how/if calculation can be interrupted. xlAnyKey is the default and it pauses whenever the user starts working. xlEscKey only pauses when the escape key is pressed. It’s used when you want to allow the user to interrupt calculation, but you want to make sure they do it explicitly and not by accident. xlNoKey prevents interruption. It seems that any time you calculate in code, you should set this property to xlNoKey, then set it back.

Full article here

My stats for June. Not quite as good as J-Walks – though my bounce rate is better!

image

jethro's picture

Excel Links

My apologies for no Excel tips in here lately. I have been asked to write some specific things that will take some time to put together as they involve explaining complex conceptual approaches to solving the problem as well as the specific excel solution. However they are coming – thanks Nicola and Traci. Anybody else want to send me ideas for posts or requests for help feel free.

excel 2007 Today I am just emptying my browser from a whole bunch of other peoples Excel posts I had found interesting and wanted to share.

This one actually answers one of Nicolas questions – kind of – what are the differences between Excel 2003 and 2007. While not specifically answering this question, Allen explains how to find the differences in VBA. Excel 2007 VBA differences

One of the most common problems we have in Excel is using dates and times in relation to calendar years and financial years. In Australia the financial year ends June 20 so financial years cross two calendar years. This also causes issues with the financial year quarters. DataPig Technologies has a post on the BaconBits Blog about Choosing Quarters in Excel.

Jan Karel Pieterse, a long time Excel MVP I have read for a long time, has a extensive Excel website. His comprehensive series on Using Range Names is very useful.

While working on a major coding project we needed to remove sensitivity to case. This article from OzGrid provided the answers we needed. Stop Excel VBA Macro Code Being Case Sensitive & Compare Text. Excel VBA Case Sensitive.