Tables Part 3: Using Formulas with Tables

re: Tables Part 3: Using Formulas with Tables

Harlan Grove — Wed, 02 Nov 2005 07:34:52 GMT

Re Biff's comment about steep learning curve: we'll all be newbies together!

Seriously, I'll stand by my earlier prediction: IT departments will look back on Office 12 as perhaps the biggest PITA upgrade in history. As I've already mentioned, the XL4 to XL5 transition, the last radical one, was no big deal because XL wasn't so widely used at that time (early 1990s).

Interesting to see whether CIOs view the benefits of Office 12 worth the training and reduced initial productivity costs. I suspect I'll still be using XL10 (2002) at work 5 years from now. However, I'll probably upgrade *one* home machine on my own.

re: Tables Part 3: Using Formulas with Tables

Biff — Wed, 02 Nov 2005 06:09:09 GMT

I consider myself to be an above average user and I can see a fairly steep learning curve going from any current versions of Excel to Excel12.

re: Tables Part 3: Using Formulas with Tables

Harlan Grove — Tue, 01 Nov 2005 23:46:25 GMT

Colin Banfield...

...

|Since we're opening up data validation here,

|the one modification I'd like to see is logic

|included to prevent data validation from

|being completely defeated by pasting data

|into cells with data validation rules.

Let's take that a bit further. Data Validation would be a LOT more useful if it were part of the recalculation infrastructure. Users can also enter formulas which initially return valid values but later return invalid values. It'd be very useful if Excel would return a new error value, #INVALID!, in such situations. Even better if #INVALID! errors couldn't be trapped by IFERROR.

[Which gets back to IFERROR and selectively trapping error values. IMO, there are three classes of error: the expected errors #N/A, #VALUE!, #NUM!, #DIV/0!, the ambiguous error #REF! and the unexpected errors #NULL! and #NAME?. The first set are often returned by built-in functions in fairly unexceptional circumstances (lookup value not found, substring not found in longer string, square root of a negative number, average of a range containing no numbers). #REF! could be returned by INDEX, INDIRECT or OFFSET passed invalid parameters (begging the question whether #VALUE! would have been more appropriate), but could also indicate model corruption (destroyed references). The last set almost always indicate model corruption or incompleteness. I can't see any good reason ever to trap #NULL! or #NAME? errors.]

Back to the issue of pasting, it does seem odd that Data Validation isn't triggered by all the actions that would trigger the Change event handler.

I suspect that back in XL8 (97) someone foresaw that pasting in large amounts of data into multiple cells at once would require a design decision about whether to handle Data validation rules separately in all affected cells (which could take a LONG TIME if lots of cells were affected), prompt to paste without applying rules or cancel pasting (something like issuing a warning and prompt like 'The destination range contains cells with Data Validation rules. Those rules can't be processed all at the same time. Proceed without applying those rules or abort the operation?' but that was before Microsoft came to believe that the user should be in control), or just ignore the rules in silence. So guess which one Microsoft chose?

Myself, being a cynic, I note that if cell A2 has custom validation rule =$A$2

re: Tables Part 3: Using Formulas with Tables

Colin Banfield — Tue, 01 Nov 2005 21:56:26 GMT

Since we're opening up data validation here, the one modification I'd like to see is logic included to prevent data validation from being completely defeated by pasting data into cells with data validation rules.

Now back to your scheduled program....

Colin

re: Tables Part 3: Using Formulas with Tables

Rob van Gelder — Tue, 01 Nov 2005 19:50:59 GMT

For Data Validation that uses criteria from a List, I'd like to see an autocomplete feature.

The feature doesn't even need to be a part of Data Validation - it just might be a convenient place to put it.

The standard in-cell autocomplete (Options: Enable AutoComplete for cell values) doesn't always activate so that's why I'd like an autocomplete on Data Validations.

On Data Validation:

I'd like the width and height of the dropdown box to be configurable.

Cheers,

Rob

re: Tables Part 3: Using Formulas with Tables

Tianwei — Tue, 01 Nov 2005 18:57:00 GMT

Harlan,

- List reference names does not work for XL2000. Good to know it works for XL2002. So I guess it will carry through 12.

- LIKE function will be highly useful. My examples are only a small faction of the wish list I have accumulated

- The auto-scrubbing function I suggested is not to limit users but rather seemlessly convert user inputs without giving annoying error messages all the time. Think of an application using a designed sheet as input source for high-school level temps. They need to input things quickly, not messing with any formula. Also again, my examples is only a small fraction of the wish list. How about auto-triming spaces? auto-add fixed strings to input (and into any position of the input)? auto-deleting any characters? auto-set hi-lo limits for numerics? auto-calculation based on certain formula (a/s/m/d, sqrt, or whatever)? In total, I hope data validation should be more of an "enabler" than just a "disabler".

re: Tables Part 3: Using Formulas with Tables

Harlan Grove — Tue, 01 Nov 2005 18:03:40 GMT

Tianwei...

...

|1. Make "List" reference not just on current

|sheet (I'm still using v2000, not sure about

|subsequent versions)

Define a name for the list, and use the defined name as the source for the drop-down. This works in XL10 (2002) and (I believe) previous versions that provide Data Validation.

This seems to prove that there's no semantic reason why data validation drop down lists can't use ranges anywhere in any open workbook. (Just like there appears to be no semantic reason for the 7 nested function call limit in current versions.) It looks like another shortsighted syntactic restriction serving no clear purpose.

|2. Add a "Like" option, then a box to specify

|the format "YR*" or "###-##-####", or a rule

|dialog box to specify user input must contain

|"@", "http://" etc.

And if Excel would ever provide a LIKE worksheet function (or regular expression support in SEARCH, as OpenOffice optionally provides in its SEARCH function in Calc), then it'd be possible to write data validation formulas using such new functions that would be able to check almost any text pattern that could be defined in prose.

|3. Provide another tab on "auto-scrubbing"

|feature, such as auto converting to upper

|case, formatting to a specified format (SSN,

|phone number etc based on numbers only input)

Adding ever more complexity in the dialogs. Formula (that nasty word!) filters could be added to convert entries (other than formulas) into some specified format. E.g., converting entries to upper case,

=UPPER(#Entry)

converting to standard US SSN format,

=TEXT(--SUBSTITUTE(SUBSTITUTE(#Entry," ",""),"-",""),"000\-00\-0000")

The former should be simple enough for most users to figure out.

Data entry masks that filtered entries and put them into the given format would be OK too, but not as powerful.

re: Tables Part 3: Using Formulas with Tables

Tianwei — Tue, 01 Nov 2005 14:35:05 GMT

David Gainer,

"Rob – Yes, data validation is copied down (any ‘property’ you can set on a cell is). No, data validation hasn’t changed. What specifically are you looking for? "

Not trying to answer for Rob. I do have a few requests on validation:

1. Make "List" reference not just on current sheet (I'm still using v2000, not sure about subsequent versions)

2. Add a "Like" option, then a box to specify the format "YR*" or "###-##-####", or a rule dialog box to specify user input must contain "@", "http://" etc.

3. Provide another tab on "auto-scrubbing" feature, such as auto converting to upper case, formatting to a specified format (SSN, phone number etc based on numbers only input)

Since Excel now hosts 1mm rows, I can see Excel more and more used as a pseudo database (DBAs will freak out but I can tell you that will be the realty), more robust data validation is very important.

re: Tables Part 3: Using Formulas with Tables

Jan Karel Pieterse — Tue, 01 Nov 2005 14:10:21 GMT

Harlan, David,

I agree with Harlan on the automatic refresh, with a sidenote:

Why not include an option in the Pivot table options: "Refresh Pivot on recalc".

re: Tables Part 3: Using Formulas with Tables

Harlan Grove — Mon, 31 Oct 2005 23:27:21 GMT

David Gainer...

| . . . the PivotTable does not auto-refresh, so

|user interaction will still be needed. Curious -

|would you like that sort of "auto" refresh

|behaviour?

Automatic recalculation is the key distinguishing feature of spreadsheets vs functional programming languages and databases. Anything in spreadsheets that fails to provide automatic recalculation is generally BAD. That includes pivot tables.

I understand why it'd be a bad idea refreshing pivot tables from outside sources, like databases, but on modern hardware refreshing pivot tables based on worksheet ranges shouldn't much worse than recalculating complex formulas that return the same results. So, yes, automaticly refreshing pivot tables based on worksheet ranges would be a GOOD thing.