COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES
 

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