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.