COPYING FORMULA CONTAINING NAMES... ( 2ND POST)

[Bricol]

(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

 

[pshepard]

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]

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!?

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.

(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

 

[Mike H.]

I don't know what was in your other post, but I am wondering why you need to
use named ranges to create a trial balance. As an accountant I have created
many trials for many different companies but I don't recall needing named
ranges. Please explain and may I can offer a different manner for
accomplishing your goal.

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!?

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.

(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

 

[Gord Dibben]

Visit JK Pieterse's site and download his Name Manager Add-in for great
flexibility in naming/renaming/manipulating range names.

http://www.jkp-ads.com/


Gord Dibben MS Excel MVP

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!?

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.

(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

 

[Bricol]

Hi Mike
The TB's arent the problem - I import them intact. What I am doing is using
them to create Management Account Reports & Analysis, and its useful to be
able to see at a glance what accounts ive
"added/subtracted/divided/multiplied" to arrive at a result without
continually referring back to my input table.
(These used to be "clean", but finding these days I often need to change as
different folk want different perspectives, and some of the relationships are
complex) Was looking for a quick fix, which my old Quattro for Dos, used to
allow me! Thx for the reply.

I don't know what was in your other post, but I am wondering why you need to
use named ranges to create a trial balance. As an accountant I have created
many trials for many different companies but I don't recall needing named
ranges. Please explain and may I can offer a different manner for
accomplishing your goal.

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!?

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.


:

(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