|
name range from absolute to relative
When you give a cell a name it defaults to absoute reference. Can you change
the reference from absolute to relative. -- Your time and consideration would be greatly appreciated. WCO |
name range from absolute to relative
Yes, follow these steps:
<Insert<Name<Define... Find your named range in the list and take out the dollar signs. -- Regards, Dave "WCO" wrote: When you give a cell a name it defaults to absoute reference. Can you change the reference from absolute to relative. -- Your time and consideration would be greatly appreciated. WCO |
name range from absolute to relative
And to remove dollar signs you would select the Refers to: box and hit F2 to get
into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
How are you removing the $ signs?
Are you in Edit mode while removing? Did you use the F4 to cycle through the options per my second post? Gord On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M wrote: To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
Thanks Charles and Gord: Gord: I believe I am in edit mode. (Is there something I'm missing?) I tried using F4 (and deleted the "$"s directly) which does change the reference type, and all seems well when I close the window, however if I access the name again to verify it, it has randomly changed the range of cells I have specified - a different range each time I view it, even without changing it. It seems fine if the reference stays absolute, but takes on a life of its own when I change it to relative. The target range is in column C. When I change the reference to relative, it changed to specify column G. When I closed the window (without changing it), and viewed the name description again, it changed to specify column H. Charles: To answer your question, here's some background - maybe there's a better way to do it anyway. I have a spreadsheet with related data, however it is broken into several ranges of rows (say 10-19, 30-39, and 50-59), where I have an arbitrary number of named columns within those rows (say ALPHA10 refers to A10-A19, BETA10 refers to B10-B19). As new parameters need to be added (say inserting a column between A and B) to rows 10-19 ONLY (thereby shifting columns B and higher to the right, in rows 10-19 ONLY), I would like to leave the existing formulae unchanged, (using the names ALPHA10 in column A and BETA10 now in column C,) so that they use the data in the existing columns (within the range of rows). In order to do this, the formulae in the shifted columns need to use relative references, thereby accessing the same data as was done before the new columns were inserted. If the reference is absolute, the formulae in the shifted columns incorrectly refer to the data in the columns to the left. (As might be expected, the reason I am naming the cells is to make the formulae more understandable and less error prone.) John "Gord Dibben" wrote: How are you removing the $ signs? Are you in Edit mode while removing? Did you use the F4 to cycle through the options per my second post? Gord On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M wrote: To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
Relative names are tricky because there is no fixed location for the
refersto, so as you change the location of the active cell/sheet the refersto formula will appear to change: you have to keep working out what the refersto formula is relative to. Relative names are easier to handle and understand if you switch to R1C1 mode because then the refersto does not change, but I really would recommend you download and install Name Manager: it does not cost you anything and makes all aspects of handling and managing names much easier. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "J M Bishop" wrote in message ... Thanks Charles and Gord: Gord: I believe I am in edit mode. (Is there something I'm missing?) I tried using F4 (and deleted the "$"s directly) which does change the reference type, and all seems well when I close the window, however if I access the name again to verify it, it has randomly changed the range of cells I have specified - a different range each time I view it, even without changing it. It seems fine if the reference stays absolute, but takes on a life of its own when I change it to relative. The target range is in column C. When I change the reference to relative, it changed to specify column G. When I closed the window (without changing it), and viewed the name description again, it changed to specify column H. Charles: To answer your question, here's some background - maybe there's a better way to do it anyway. I have a spreadsheet with related data, however it is broken into several ranges of rows (say 10-19, 30-39, and 50-59), where I have an arbitrary number of named columns within those rows (say ALPHA10 refers to A10-A19, BETA10 refers to B10-B19). As new parameters need to be added (say inserting a column between A and B) to rows 10-19 ONLY (thereby shifting columns B and higher to the right, in rows 10-19 ONLY), I would like to leave the existing formulae unchanged, (using the names ALPHA10 in column A and BETA10 now in column C,) so that they use the data in the existing columns (within the range of rows). In order to do this, the formulae in the shifted columns need to use relative references, thereby accessing the same data as was done before the new columns were inserted. If the reference is absolute, the formulae in the shifted columns incorrectly refer to the data in the columns to the left. (As might be expected, the reason I am naming the cells is to make the formulae more understandable and less error prone.) John "Gord Dibben" wrote: How are you removing the $ signs? Are you in Edit mode while removing? Did you use the F4 to cycle through the options per my second post? Gord On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M wrote: To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
Thanks for your reply, and for the Name Manager. I can see it will be useful. So far it has shown me that the "random" behavior is not that random, and is related to your comment: Relative names are tricky because there is no fixed location for the refersto, so as you change the location of the active cell/sheet the refersto formula will appear to change: you have to keep working out what the refersto formula is relative to. Obviously I don't quite understand that yet - how can that possibly be useful? This behaviour is confirmed by updating the Name Manager window after moving the cursor to a different cell. ALL of the relative references change for ALL of the names when I click on a differents cell. What I am looking for is a way to define the names with "relative" references. (Until now I thought I was using the right term.) In this name definition, I could name cells using a relative description, and then insert columns in between previously named columns and still have results calculated based on the previously referenced cells. I would like to be able to do something like: - name column A "NameA" - name column B "NameB" - define cells in column C to be "=NameA+NameB" - insert a new column between columns A and B - have cells in (now) column D to be "=NameA+NameB", with the same result as before I don't think R1C1 mode is going to help me in this case. Is there another way to do this? John "Charles Williams" wrote: Relative names are tricky because there is no fixed location for the refersto, so as you change the location of the active cell/sheet the refersto formula will appear to change: you have to keep working out what the refersto formula is relative to. Relative names are easier to handle and understand if you switch to R1C1 mode because then the refersto does not change, but I really would recommend you download and install Name Manager: it does not cost you anything and makes all aspects of handling and managing names much easier. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "J M Bishop" wrote in message ... Thanks Charles and Gord: Gord: I believe I am in edit mode. (Is there something I'm missing?) I tried using F4 (and deleted the "$"s directly) which does change the reference type, and all seems well when I close the window, however if I access the name again to verify it, it has randomly changed the range of cells I have specified - a different range each time I view it, even without changing it. It seems fine if the reference stays absolute, but takes on a life of its own when I change it to relative. The target range is in column C. When I change the reference to relative, it changed to specify column G. When I closed the window (without changing it), and viewed the name description again, it changed to specify column H. Charles: To answer your question, here's some background - maybe there's a better way to do it anyway. I have a spreadsheet with related data, however it is broken into several ranges of rows (say 10-19, 30-39, and 50-59), where I have an arbitrary number of named columns within those rows (say ALPHA10 refers to A10-A19, BETA10 refers to B10-B19). As new parameters need to be added (say inserting a column between A and B) to rows 10-19 ONLY (thereby shifting columns B and higher to the right, in rows 10-19 ONLY), I would like to leave the existing formulae unchanged, (using the names ALPHA10 in column A and BETA10 now in column C,) so that they use the data in the existing columns (within the range of rows). In order to do this, the formulae in the shifted columns need to use relative references, thereby accessing the same data as was done before the new columns were inserted. If the reference is absolute, the formulae in the shifted columns incorrectly refer to the data in the columns to the left. (As might be expected, the reason I am naming the cells is to make the formulae more understandable and less error prone.) John "Gord Dibben" wrote: How are you removing the $ signs? Are you in Edit mode while removing? Did you use the F4 to cycle through the options per my second post? Gord On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M wrote: To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
name range from absolute to relative
Not sure I understand your problem: what you describe you want sounds to me
like standard Excel behaviour anyway. If you want a formula to refer to an absolute column (always column C regardless of how many columns get inserted before C), then use a function like INDIRECT or OFFSET or INDEX (INDEX is non-volatile but the other 2 are volatile). If you want a formula to always refer to the same data/result even when that data/result gets moved by column/cell insertions then just use standard referencing, Excel will change the formulae to handle the movement. If you want to use Relative Names then its best to switch to R1C1 notation because a relative reference in R1C1 notation DOES NOT CHANGE when you change the active cell, so its easier to see and understand what you and Excel are doing. Of course you get exactly the same results with r1c1 as with a1, its just easier to understand the refersto formula because it directly shows you the relative offsets ( R[-1]C[-2] refers to the cell one row above and two columns to the left etc). We added the R1C1/A1 toggle to the Name manager EUI (bottom right options) to make it easier to flip between the 2 display modes precisely because of this. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "J M Bishop" wrote in message ... Thanks for your reply, and for the Name Manager. I can see it will be useful. So far it has shown me that the "random" behavior is not that random, and is related to your comment: Relative names are tricky because there is no fixed location for the refersto, so as you change the location of the active cell/sheet the refersto formula will appear to change: you have to keep working out what the refersto formula is relative to. Obviously I don't quite understand that yet - how can that possibly be useful? This behaviour is confirmed by updating the Name Manager window after moving the cursor to a different cell. ALL of the relative references change for ALL of the names when I click on a differents cell. What I am looking for is a way to define the names with "relative" references. (Until now I thought I was using the right term.) In this name definition, I could name cells using a relative description, and then insert columns in between previously named columns and still have results calculated based on the previously referenced cells. I would like to be able to do something like: - name column A "NameA" - name column B "NameB" - define cells in column C to be "=NameA+NameB" - insert a new column between columns A and B - have cells in (now) column D to be "=NameA+NameB", with the same result as before I don't think R1C1 mode is going to help me in this case. Is there another way to do this? John "Charles Williams" wrote: Relative names are tricky because there is no fixed location for the refersto, so as you change the location of the active cell/sheet the refersto formula will appear to change: you have to keep working out what the refersto formula is relative to. Relative names are easier to handle and understand if you switch to R1C1 mode because then the refersto does not change, but I really would recommend you download and install Name Manager: it does not cost you anything and makes all aspects of handling and managing names much easier. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "J M Bishop" wrote in message ... Thanks Charles and Gord: Gord: I believe I am in edit mode. (Is there something I'm missing?) I tried using F4 (and deleted the "$"s directly) which does change the reference type, and all seems well when I close the window, however if I access the name again to verify it, it has randomly changed the range of cells I have specified - a different range each time I view it, even without changing it. It seems fine if the reference stays absolute, but takes on a life of its own when I change it to relative. The target range is in column C. When I change the reference to relative, it changed to specify column G. When I closed the window (without changing it), and viewed the name description again, it changed to specify column H. Charles: To answer your question, here's some background - maybe there's a better way to do it anyway. I have a spreadsheet with related data, however it is broken into several ranges of rows (say 10-19, 30-39, and 50-59), where I have an arbitrary number of named columns within those rows (say ALPHA10 refers to A10-A19, BETA10 refers to B10-B19). As new parameters need to be added (say inserting a column between A and B) to rows 10-19 ONLY (thereby shifting columns B and higher to the right, in rows 10-19 ONLY), I would like to leave the existing formulae unchanged, (using the names ALPHA10 in column A and BETA10 now in column C,) so that they use the data in the existing columns (within the range of rows). In order to do this, the formulae in the shifted columns need to use relative references, thereby accessing the same data as was done before the new columns were inserted. If the reference is absolute, the formulae in the shifted columns incorrectly refer to the data in the columns to the left. (As might be expected, the reason I am naming the cells is to make the formulae more understandable and less error prone.) John "Gord Dibben" wrote: How are you removing the $ signs? Are you in Edit mode while removing? Did you use the F4 to cycle through the options per my second post? Gord On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M wrote: To remove the dollar signs would seem to be the logical thing to do, however I've tried this on two computers and in both cases the new "refers to" formula references a random range of cells - different each time I try. As an example, if I remove the "$"'s (all four) from: =Sheet1!$C$11:$C$16 I get: =Sheet1!IT16:IT21 John "Gord Dibben" wrote: And to remove dollar signs you would select the Refers to: box and hit F2 to get into edit mode. Select the referenced cells and F4 to cycle through the options untill you get to relative reference. Gord Dibben MS Excel MVP On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier wrote: Yes, follow these steps: <Insert<Name<Define... Find your named range in the list and take out the dollar signs. |
| All times are GMT +1. The time now is 01:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com