Formula building improvements Part 4: Defined Names

re: Formula building improvements Part 4: Defined Names

Stephen Bullen — Mon, 31 Oct 2005 13:10:16 GMT

Name dialog: Please also show the Refers To for each name as a column in the box, so I don't have to select a name to see what it refers to. Most of the time (as a developer) I'm much more interested in its Refers To formula than its value or comment.

Name comments: It would find them most useful if the Filter dropdown could filter on a textual wildcard match of the comments. I could then use the comment for categorisation of names, or if I'm including change logging, I could easily find the name(s) I changed yesterday. Lastly, in most cases, the comment would be for the developer, not the end-user, so I wouldn't want the comment (for a specific name) to appear as tooltips etc.

re: Formula building improvements Part 4: Defined Names

David Gainer — Wed, 26 Oct 2005 04:21:15 GMT

Harlan - No, it wont.

Roger - If you are ok writing some VBA, you could put pretty much anything you want in the comment, including the name's refer to.

Nigel - Thanks for the feedback.

Tianwei – Great. We have exactly the solution for you in Excel 12 – see most recent post (“Tables Part 1”) … more details on how this works coming later this week.

Erik – Thanks for your feedback. Custom data isn’t something that have addressed directly in Excel 12, but it is on my list of things to think about for future versions.

re: Formula building improvements Part 4: Defined Names

Erik Westwig — Tue, 25 Oct 2005 19:11:21 GMT

I create hidden names and sheets all the time, so I have mixed feelings about letting users see them. That being said, I find that I'm moving away from xlVeryHidden when I create hidden sheets. It's so much easier to tell a customer how to unhide it, if I want to help them debug some problem on the phone, or whatever. So, with reservations, I think I'd vote for letting the users view the hidden names if they really want to dig.

One thing about names I noticed in previous Excel versions is that the larger the name table gets, the longer it takes to add or remove a single name. I haven't checked this in the most recent Excel versions, but I remember (in Excel 2000?) that the slow down was pretty much linear with the size of the name table. Many times I have started to implement some functionality using hidden names, only to revert to a hidden sheet scheme because I realized I simply had too much data to write, and the slow down was too severe. Eventually, I created a rule for myself that I would never implement any code that required the creation or deletion of more than about 1000 names in a workbook. Of couse I haven't rechecked my rule with newer Excel versions.

All this talk about hidden names and hidden sheets makes me think about what we all seem to use these things for. Their primary purpose seems to be to attach persistence information to cells, worksheet, and workbooks in a way that will update as the user makes changes to the workbook. And in many ways the hidden names and hidden sheets work well. But in many ways they are problematic.

For example, they don't really handle copy and paste very well. If I want to attach some hidden info to a cell, I can do so. But if the user copies and pastes that cell to another location, there is only one hidden name. To get around this problem, I've play with things like encoding information into a cell's custom formatting fields and similar ridiculous schemes.

What I'd really like to see it an Excel sanctioned method for storing custom data in a cell (or really, with any object. I've played games to store hidden data in drawing shapes too.) This is sort of like the old "BigData" structure in the XLL interface. But unlike "BigData" there would hopefully be a way to keep references and formulas updated as the user made changes to their model. And the data could get copied when the object was copied, etc.

Just a thought...

re: Formula building improvements Part 4: Defined Names

Tianwei — Tue, 25 Oct 2005 14:56:52 GMT

"Jean, Gary, Tianwei – what is the scenario you have in mind when you say “dynamic ranges”? I will be talking about some work we have done around tables starting tomorrow that might be helpful to you. "

One of the scenario is charting. When user add data the chart will automatically pick up the data. John Walkenbach has done a wonderful tip here: "j-walk.com/.../tip053.htm"

Would like to see that as standard.

re: Formula building improvements Part 4: Defined Names

Nigel Harper — Tue, 25 Oct 2005 10:12:53 GMT

Dave,

Thanks again for giving us our daily bread in the way of this blog.

I disagree with Jim Rech about the scope suggestions both in creating new ones and showing existing ones in the new UI. When a name is created I often want to create the same name, say on 12 sheets for the months of the year. We should be able to create a name once but make it local to one or any number of specific sheets at the same time by some sort of checkbox list. However I agree that we don't necessarily want a list of a zillion names so the Filter should be able to show:

'Names Scoped to Activesheet'

'Names Scoped to All Worksheets'

'Names Scoped to Workbook'

For dynamic named ranges (especially those using formulas to construct addresses and refer to them via the INDIRECT function) it would be very useful to have displayed the RefersToRange property somewhere in the UI.

Also, the UI must have a way of excluding some names from the general users view. I agree that we should be able to create and hide a name via the UI, but I also concur that a xlVeryHidden VB switch should be included for access via the VBE, I believe this to be very important.

Thank you,

Nigel

re: Formula building improvements Part 4: Defined Names

Roger Crawley — Tue, 25 Oct 2005 10:01:36 GMT

David,

What I meant by customise was a group of radio buttons within the Name Manager so that each user could decide what they want the Name Comment to tell them.

Is there no way of programming the Name Comment to reflect the current "refers to" property? A manual paste solution is fine on the rare occassion that a worksheet is not going to have any rows/columns inserted/deleted.

re: Formula building improvements Part 4: Defined Names

Harlan Grove — Tue, 25 Oct 2005 09:02:32 GMT

Should have thought to ask this before - will the new Manage Names dialog show worksheet scope names in VERY hidden worksheets?

re: Formula building improvements Part 4: Defined Names

David Gainer — Tue, 25 Oct 2005 01:43:48 GMT

Thanks everyone for the comments and discussion and feedback on hidden names.

Rob - The in use-column indicates whether a name is being referenced in a cell, or by another name in the active workbook. It doesn’t look at the following to determine if a name is in use or not:

- names that are referenced as strings via INDIRECT (not parsing this string)

- names that are referenced in VBA (not possible to parse the code manually and accurately pull out defined names).

- names that are referenced by other workbooks (external references)

Hazz – The current limit is ~1,000. Also, the New/Edit Name dialog is resizable, thanks for helping me point that out. 3D ranges for SUMPRODUCT has not changed from Excel 2003.

Jean, Gary, Tianwei – what is the scenario you have in mind when you say “dynamic ranges”? I will be talking about some work we have done around tables starting tomorrow that might be helpful to you.

Harlan – Yes, you diehard keyboard users will still be able to create worksheet-scoped names the way you do today. We are very aware of keyboard users on the Excel team. We have not changed the limit for defined names in Excel 12.

Jim – The current design does not allow users to create hidden names. The goal of that filter option is to help users troubleshoot their workbooks (hidden names copied over from other workbooks can cause problems and this would allow saavy users to find those names and remove them if necessary). I understand, however, the concern of developers who use hidden names in their apps not wanting customers to have control over them. Thanks for the feedback on the scope drop down. One goal of this dialog was to allow users to do workbook-level operations, but yoru point is well taken.

Jan Karel – Thanks for the suggestions.

Roger – You can manually paste the “refers to” contents into the name comment, but there is no way to link them. You can type pretty much whatever you want into the comment box – is that what you meant by customise?

Mpemba – Yes, the name drop-down will be wider by default and resizable. See my post on the formula bar.

Tianwei - Stay tuned for upcoming posts on tables. Then let me hear if we have addressed your scenario around names for column headings.

Alexandre - Just to make sure I understand, do you mean you would like a sample of the file format?

re: Formula building improvements Part 4: Defined Names

Colin Banfield — Tue, 25 Oct 2005 00:21:57 GMT

I use the trace precedent and trace dependent arrows for this purpose but it sounds like what you want is a name/cell reference display toggle like the A1/R1C1 display toggle or the cell result/formula display toggle.

Colin

re: Formula building improvements Part 4: Defined Names

Biff — Mon, 24 Oct 2005 22:12:49 GMT

"I find it incredibly frustrating when named ranges are used for almost everything. Whilst it may make understanding formulae easier (IF sensible names are used following a consistent convention). However, for auditing formulae I find it more of hindrance because you cannot the identify ranges used. "

I couldn't agree more.