Quote Originally Posted by kpuls
Hey everyone!

The timliness of this is quite amusing, I think. Just got Ozgrid's May Newsletter which, lo and behold, features Dave's opinions on Named Ranges!

Dave,

To quote your newsletter (with regard to range names),
"Using them frequently is a very good habit to form."

I would say, perhaps, in the context as you present them. People in general say, "Yes, use them. They're great; and here's why..."


I think it's safe to say the largest crowd out there using spreadsheets are in the accounting/finance fields. In practice, when people in this field develop models, typically the models are not forever self-contained. In fact, most often sheets or modules of a model are copied/reused and moved from app to app. The pitfalls associated with this type of activity on an ongoing basis as it relates to prolific use of ranged names is seldomly if ever discussed when suggesting that people in general should use them habitually.

I would like to begin to see at least some precautionary statements associated with using named ranges in spreadsheet formulas rather than just telling people, "Hey, these things are great and you should use them a lot!"

I seem to be in the minority of excel developers (with some financial modelling background) who look further down the train track and notices that in the accounting/finance field range names have a tendency to lead to derailment. Unfortunately, at least in my case, it seems like I'm having to deal with the train wrecks after the fact on a more regularly occuring basis. Perhaps some of the wreckage could be avoided if newbie developers were given a headsup on some of the dangers instead of just the typical, "Here they are; go use em!" message.

Generally speaking... I say yeah, range names have their uses. I typically have 10-15 or so in my models used for just formula reference. Typically another 10 or so for VBA refs. I pre-qualify all my VBA only range names as "VBA_MyRangeName" so I know which ones relate to macros at a glance. When I work with new analysts on modelling fundamentals, I'm careful to point out that range names should be treated with extra caution. My experience has been that the newbie developers are unaware of the pitfalls.