Range Names: Good or Bad?

curious question ...

as a matter of fact defined named ranges are good

it's very usefull for several reasons

here is one reason
used within formulas it's much more clear what formulas are supposed to do
=(A1-reduction)*VAT
=(A1-$Z124) * Sheet1!$E453

another
when you code
Msgbox "the VAT is actually " & Range("VAT") & "%"
this will always be OK
when you use Sheet1!$E453 and change the address your code iwll not work anymore

kind regards,
Erik

PS: some of you just don't respond on the other boards : isn't it their right to select the questions they want to answer ?

I think named ranges are outstanding. They have a number of advantages.

1. If I have 100 formulas that refer to a given range. I can just redefine the range, and I don't have to change a single formula. (also convenient for graphs where the data range changes.)

2. If I want a drop down list, I can use a named range to refer to cells that are on another worksheet.

3. If I have a really long reference, like to another sheet I can use a short name range. This makes a long formula much shorter and much easier to decipher. (it's also useful if you run into the 1024 character limit for formulas.)

4. Named ranges can be more intuitive when writing a spreadsheet and can make the logic of a spreadsheet more transparent.

5. See Aladins post about dependent drop down menus. (only possible with named ranges.)


Named Ranges = Good Practice
[Edit:]

This:
=SUM('Really Long Named Sheet of Info'!$IV$25000:$IV$65000)^('Really Long Named Sheet of Info'!$IU$20000+'Really Long Named Sheet of Info'!$IU$20000+1)

Can become this:
=A^(B+B+1)

Where A and B are named ranges.

Most often I create dynamic ranges that will adjust when adding or removing information. It really depends on who the user will be, and will they ever see the named range and wonder: "What the hell does this mean?"

It has is purposes, and I tend to use them sparingly and prefer not to in formulas, unless the formula will need to be adjusted according to data entered. When I build something, sometimes I use none, other times I use quite alot. Just depends what purpose they serve in the individual application, and what ever seems easier to update later on.

I saw alot of people at work who, when showed how to name a range, used it like it was going out of style and now have named ranges all over the place in their workbook, wonder why they sometimes get those notorius "ghost links" that they can't get rid of.

Good Points / Bad Points

some philosophy

a pillow is a good thing when used for sleeping ... but can be used for very bad things ... brrr

Aaron Blood said:

Curious to hear your thoughts on range names.

1. Do you think defined range names in spreadsheets are good or bad?

2. Would you recommend defined range names as a modelling best practice?


Forgive the cross posting to: MrExcel, OzGrid, Vbax
(some of you just don't respond on the other boards and I don't want to miss the opinions of certain folks I respect on each)

Click to expand...

I really like using defined names and defined formulae, especially when I need to nest names within other names. I find it cleans up the UI for the viewer. Rather than having a three line formula bar, I have a quick description of what the formula is.

Editing these can be a major hassle, though, especially with the nested ones. There is a definite downside.

For a sophisticated user, I would rate them Good. For a novice or intermediate user, I would rate them Bad. An inexperienced user needs to learn the basics of working through a worksheet/workbook and learning cell referencing in the formula construction. Defined names mess that up. Or, more precisely, the name can obscure what is going on in Excel.

Once the user is comfortable with referencing, then the names can be a powerful tool to use. I find that they only serve to confuse the inexperienced user.

Just my $0.02.

Fixed (absolute) ranges are simply lovely. However I would use more than a single letter (or letter and digit) for their names, to avoid confusion with column or cells.

Dynamic (relative, not absolute) ranges are cute and fun to show off and generate pride. When you make one you well might call everyone in the office or neighborhood over to show it off, because they can be vastly, vastly powerful in compacting/simplifying references. And ... they are almighty hell to maintain, in many circumstances. They [to a degree] defeat dependency auditing. If you use Intersect to determine if a cell is in a range - who knows what you'll get with those dynamic ranges. As I recall, F5 does not recognize them. And if you look at the defined range dialog, the address it "refers to" is as reliable as a Clinton answer to a Senate investigation. Move one cell to the right, and it "parses a different answer." Slithery little buggars!

Unless you're in a very well documented environment, or are the person who created the range, often you're not aware of these shifty animals; I have personally outlawed relative ranges for any reusable or portable or maintained-by-another workbook. Yet I fully appreciate the immense convenience they can provide otherwise.

95% of the Excel work I do is for my own projects. I normally start developing the workbook with straight ranges. I troubleshoot everything with specific ranges to make sure it works, play with it some more and get the bugs ironed out, then I move to named ranges, especially dynamic named ranges.

On those occasions when I help someone else setup a workbook, I follow the same procedures, but walk them through the steps of using named ranges. Then I help them troubleshoot, and finally let them troubleshoot on their own.

Of course, I am not an Excel developer, I just get asked to help many others in the company "solve a little problem".