Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
COPYING FORMULA CONTAINING NAMES... ( 2ND POST)
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
COPYING FORMULA CONTAINING NAMES... ( 2ND POST)
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. "Bricol" wrote: 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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
COPYING FORMULA CONTAINING NAMES... ( 2ND POST)
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 On Mon, 14 Jul 2008 02:57:00 -0700, Bricol wrote: 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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
COPYING FORMULA CONTAINING NAMES... ( 2ND POST)
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. "Mike H." wrote: 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. "Bricol" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPYING FORMULA CONTAINING NAMES... ( 2ND POST) | Excel Worksheet Functions | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Copying names and frqequency of occurrences. | Excel Worksheet Functions | |||
Copying document names? | Excel Discussion (Misc queries) | |||
copying cell names | Excel Discussion (Misc queries) |