Range Names : Good or Bad programming practice?

[rberke]

I usually don't bother with named ranges in simple spreadsheets which will be thrown away in a day or two.



Almost all of my other spreadsheets rely heavily on named ranges. Especially useful for my a spreadsheets with dozens of columns. As far as I am concerned defined names greatly simply almost every spreadsheet task. For instance, I don?t like the way the columns are arranged, I?ll just cut column Q and paste it in front of column C, and everything including vba keeps working without any problems. (see my comment a little later about vlookup)





For instance, many of my worksheets have a header area followed by a detail area followed by a summary row then a trailer area. The detail area might have 20 columns in a database format. I would define

Hdr=1:14

all = 15:50

sumrow=51:51

trailer= 52:60

targetSales=A:a

TodaysSales=b:b

YesterdaysSales=c:c



I'll then make references to =Sum(all dailysales). Or perhaps =?the bottom line is? & range(?sumrow target?)

For the most part it works great.



Its also amazing how easy it is to sort thing using range names:



Edit goto all (I key alt g "all" <enter>)

data sort daily sales. ( I key alt d s "dailysales" <enter>



I don't ever touch the mouse for lots of these quick tasks.



Cell formulas become much easier like =if(sales>2*target,"great",if(sales>target,"good","bad))



These formalas copy down to the whole range very nicely.



I also have developed routines that allow copy chunks of 5 spreadsheets onto one spreadsheet with automatic resizing before print. That was a truly cool application because the copies automatically update and resize before printing Couldn't have done any of it without defined ranges.

I even use the defined field inside vba a ton. For instance the following seems very straightforward to me and allows me to reference spreadsheet cells very easily.



For r = 1 to 50

ActiveWorkbook.Names.Add Name:="curr", RefersToR1C1:=rows(i)

If range(?curr today?) < range(?curr target?) then ?.



Next r



If there are two or three detail areas, things works just as well.

AllSales=15:50

AllPayments=65:80

TodaysPayments=a:a.



Notice, I don?t worry about the fact that there are two different column names for a:a (TodaysPayments and TargetSales). It just all works itself out automatically. Sum (Allpayments TodaysPayment) gets one set of cells and sum(allsales targetsales) gets a different set.





But (Very rarely) it defines ranges do cause troubles:



Problem 1: I have to be careful when I insert a row at the bottom or top of a range. I sometimes solve that by inserting an placeholder row which is included in the range and is always empty. I make it two points high and all black shaded, then forget about it.



Problem 2: I have to avoid using Max and Min functions. If cell d20 contains =if(target < max(Today, Yesterday)>target,?Good?, ?Bad?) then it will be evaluated incorrectly like =If(a20< max(b1:b65000, c1:c65000), ?good?, ?bad?)



This surprising behavior only hits me with min and max functions, but I?m sure it could occur elsewhere.



But everything in excel has to be used carfully. For instance, a lot of people are in love with vlookup. But if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false).



I always use the match and index functions to avoid the vlookup problem. I would Name lookupkey=A:A lookupanswer=B:b and then use

=Index(lookupanswer,match(a2,lookupkey,false). Now I can enter a column anywhere I want and things work just fine.



Sorry to be so long winded, but you can see I very enthusiastic about named ranges.



In fact, I must say I was shocked to find so many people that don?t like them.

[rberke]

I forgot my other favorite vba trick:

Salesix=range("sales").column
targetix=range("target").colum
for r = 1 to 50
if cells(r,salesix) < cells(r,targetix) then....
next r

[Ken Puls]

...if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false)

Unless you use a "Match" function to return the column index.

[Aaron Blood]

But everything in excel has to be used carfully. For instance, a lot of people are in love with vlookup. But if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false).

I always use the match and index functions to avoid the vlookup problem. I would Name lookupkey=A:A lookupanswer=B:b and then use

=Index(lookupanswer,match(a2,lookupkey,false). Now I can enter a column anywhere I want and things work just fine.

It's the index in the VLOOKUP that prompts me to avoid using it in general. The regular ole LOOKUP function seems to be better suited to maintaining its lookup column point of reference. In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

[Ken Puls]

It's the index in the VLOOKUP that prompts me to avoid using it in general. The regular ole LOOKUP function seems to be better suited to maintaining its lookup column point of reference. In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

Maybe we should be starting a new thread on Lookup vs Vlookup? (Vlookup is my preferred method )

[Ken Puls]

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!

[Aaron Blood]

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.

[Ken Puls]

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.

Those are some great points, Aaron. I would have to agree with you on all of them.

[Cyberdude]

With all the conversation going on, I started getting a little confused about what's being called a "named range". I think the term range is what's bothering me. I thought you were talking about using what I call a "defined name", which is not restricted to a range. In fact all defined names are formulas, which may or may not include a range.
There is also the effect of using a column header as a name for everything beneath it. Is that perhaps what this is all about??

[rberke]

Aaron, LOOKUP function requires that the vector or array be sorted. And, you can't tell it to require an exact match. The exact match is critical in most of my lookups, so I stick to the MATCH function.

Sometimes I'll use VLOOKUP for temporary spreadsheets, but never for something that is long lasting. It is too likely that I'll come back a month later, insert a new column, and not notice that I have broken the vlookup functions.

[Zack Barresse]

There is also the effect of using a column header as a name for everything beneath it. Is that perhaps what this is all about??

That is a label.

.. insert a new column ..

Inserting and deleting, imho, is bad practice altogether. The best solutions are those where we don't have to do this 'last resort' unless absolutely necessary. Imho, it should only be used during spreadsheet creation/development and no further. But this is conversation for another thread.

This is an interesting thread, isn't it.

[rberke]

Cyberdude, Perhaps I am using terminology wrong, but I have never seen a dictionary definition that pins these things down.

When I refer to Defined Ranges or Named Ranges, I really mean a particular type of Defined Name. It seems clearer than saying "a defined name that points to a range".

I have also seen people refer to dynamic vs static ranges. Most of this discussion revolves around static ranges. If someone wants to make the range bigger, they insert a row at the bottom, and the static range gets bigger.

Dynamic ranges are slightly different, if someone wants to make the range bigger, they simply key data into the next empty cell beyond the range. Like magic, the range gets big enough to include the newly keyed cell. No need to use the "insert row" function.

[rberke]

True, the column heading is a label, but it is wrapped up in Excel's "natural language" functionality. To a certain extent it is more flexibile than and eliminates the need for defined names. For instance these column headings:
north ............... South
Monday Tuesday monday Tuesday
1.........1..............3..........2
2.........1..............1..........1

Can be referenced as =sum(north monday) and would equal 3. This happens without defining any names! Also works with Row headings and combinations of both!


But, I have had some troubles when I tried natural language, so I stopped trying long ago. Even if it worked, it seems like magic to me, so I would probably never trust it.

What I have done instead is write a vbamacro that takes row 1 of my worksheet and generates the defined name for entire columns. The macro names the ENTIRE column, not just the detail portion. So it would say target=A:A instead of RealTarget=a15:a50. When I want to refer to the real target, I would say Range("AllSales Target").
Some people might dislike this approach, but it works for me. In particular, adding an extra row to the detail area, doesn't force excel to change the definition of all my defined column names.

[rberke]

That is a label.



Inserting and deleting, imho, is bad practice altogether.

firefytr: When I first started using excel I would have agreed. It seemed that every time I inserted, deleted, or moved a column things stopped working. Then I discovered named ranges, and stopped using vlookup.

Now, I love rearranging my spreadsheet to suit my needs. I even have a "worksheet A" that automates the rearrangment process, so I can look at the columns one way when analyzing past sales, and another way when I am anayzing product costs. I'll admit this macro originally rearranged the header area more than I would like. Then I figured out a great way to put the header information in another worksheet B and paste a linked picture at the top of my worksheet a. So rearranging the columns of worksheet A does not affect the header information.

But, I still agree that shared worksheet used by serveral different users, should not be rearranged too often unless you are willing to make a macro to return things to a default sequence.

[Bob Phillips]

With all the conversation going on, I started getting a little confused about what's being called a "named range". I think the term range is what's bothering me.

You are right, they are just called Names in help. They apply to a workbook or a worksheet. They do not apply to ranges, just are most often use to ReferTo a range.

In fact all defined names are formulas,

No, names can refer to a constant value.

[Aaron Blood]

Aaron, LOOKUP function requires that the vector or array be sorted. And, you can't tell it to require an exact match. The exact match is critical in most of my lookups, so I stick to the MATCH function.

Sometimes I'll use VLOOKUP for temporary spreadsheets, but never for something that is long lasting. It is too likely that I'll come back a month later, insert a new column, and not notice that I have broken the vlookup functions.

Really? Wow... hold on... Lemme go write that down.

OK then fine, index/match in place of a VLOOKUP, data sorted, exact match... blah blah blah.

More meant as a warning against hardcoding the col offset in a vlookup. I did once notice another interesting technique with the VLOOKUP though. Lemme see if I can remember it... involved using an anchored count or something like the following...

=VLOOKUP(G1,A1:E10,COLUMNS(A11),FALSE)

I guess something along these lines would probably be safe, no?

Edit: HAH! look at that it coverted my range ref to a smiley... I'd change it, but I kind of like how the formula is laughing.

[rberke]

*1* It's the index in the VLOOKUP that prompts me to avoid using it in general

??

*2* In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

??

*3* =VLOOKUP(G1,A1:E10,COLUMNS(A:B),FALSE )

*1* I agree with completely .

*2* You have just retracted (albeit sarcastically) so we also agree on that.

*3* Been there! Done that! Doesn?t work! Column B can only be cut and pasted in the 5 column range. Go ahead -- just try pasting column B to the left of column A. Write it down!

[Ken Puls]

Okay, so if I can tie the vlookup and name thing together...

The attached workbook contains a table (named rngTblData), and the header row is also named (rngTblHead). There are two data cells (put in your year and a validation list helps you look up a value.)

The vlookup uses the match function to get the column index:
=VLOOKUP(B11,rngTblData,MATCH(B12,rngTblHead,FALSE),FALSE)

Insert a new column. It will still work, as the range is dynamic!

Seems to me that both vlookup and the named ranges work just great together in this instance.

[Aaron Blood]

*3* Been there! Done that! Doesn?t work! Column B can only be cut and pasted in the 5 column range. Go ahead -- just try pasting column B to the left of column A. Write it down!

True... true...
Now I can get back to hating range names...

[rberke]

Seems to me that both vlookup and the named ranges work just great together in this instance.

named ranges work fine with almost every excel function, including vlookup. (I discussed problems with max and min earlier).

But, I modified your example a little to show the problem with vlookup. Open the attached, and select profits column. Cut and paste it to the left of year column. Your formula breaks, mine continues to work. Mine uses index/match:
=INDEX(INDIRECT(B12),MATCH(B11,year,FALSE))

Of course, when I see how your application fits together, I believe it is one of those cases where vlookup is the best way to go. Aaron, you can now gloat!

Attachment is in following post on page 6.