Thx for the post - that worked fine once i got the hang of it :)
but ( always a but...)
Is there no way of changing the default, or a utility to "change all" ?
My input table is a Trial Balance consisting of aprox 850 lines. I used
"Create from Selection" to name each line, and thats an awful lot of range
names to go & edit individually in Name Manager!?

"pshepard" wrote:

Hi Bricol,

You can edit the range names in the name manager - remove the $, and the
range moves when you copy the formulas after that.


"Bricol" wrote:

(2nd Post - Also under General but thought this may be more applicable)
I am an old quattro user who has emigrated to Excel 2007, and being
frustrated by Excel Range names.
My apologies if this has been asked before, but I couldnt find.
-Basically, I have two columns of figures(Say "Month Sales" & "Year To Date
Sales"
-I need to set up a report based on these figures with various
ratios/comparisions
-I have named the 1st column cells so that I can keep track of what I am
adding/dividing etc
-Having completed Analysis of the "Month Sales", I now need to perform exact
same analysis of "Y-T-D Sales.
-In Quattro(for Dos), I could now simply copy my formulae into the next
column, and formula would look at 2nd column of data, s long as I hadnt put a
"$" in front of the range name in my formula. In Excel, the range name
appears to be "absolute" by definition, and formulae continue to refer back
to column 1 :(

Any suggestions? The report in question is about 60 pages long, and I cant
see any other way of doing it other than retyping all the formula!? The only
reference in the Help files I can find is a note "that Range names are by
DEFAULT Absolute". How can I change this?

eg
A1 = 100 {Rangename = ABCSales}
B1 = 657 {not named}

Report:
H10 = "=ABCSales" [Heading MONTH SALES]
I 10 = " =B1" [Heading YTD SALES] after copying