Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges with dependencies
I've started converting some charts I have whose data gets
updated very frequently so that the charts update their data ranges automatically. For example, there is a method outlined he http://www.j-walk.com/ss/excel/usertips/tip053.htm That uses the "Name" feature (Insert / Name / Define) to set up ranges that rely on COUNTA to see how long the range is. Cool. I'm interested in simplifying it further. I have many charts with many series. They all rely on the same (dynamic) length in the data range. So it seems kind of silly to define a bunch of different names, all of which are very similar except for the column number. I now have stuff like this: Name Value Unrealized.Rows =COUNTA('Current CSV'!$A:$A)-2 Unrealized.DaysHeld.Range ='SheetU'!$N$2:INDEX('SheetU'!$N:$N,Unrealized.Row s) Unrealized.Quote.Range ='SheetU'!$J$2:INDEX('SheetU'!$J:$J,Unrealized.Row s) Unrealized.GLRunning.Range ='SheetU'!$M$2:INDEX('SheetU'!$M:$M,Unrealized.Row s) Unrealized.PPS.Range ='SheetU'!$F$2:INDEX('SheetU'!$F:$F,Unrealized.Row s) Then, in the chart, I have series such as one called "Gain/Loss Running Tally" whose range is: ='SheetU'!Unrealized.GLRunning.Range and so on. Works fine. I want to have a Name or a user-defined function that lets me state the column number when I invoke the function. Then I could just have one repeating entry in the chart for each series. The "Gain/Loss Running Tally", "Days Held", and "Price Paid" series in my chart could just be, respectively, ='SheetU'!Unrealized.Range("M") ='SheetU'!Unrealized.Range("N") ='SheetU'!Unrealized.Range("F") where the arguments are the column numbers. Then I wouldn't need eight or so names for each type of chart I'm producing. I don't know how to create such a user-defined function, however! Help would be very much appreciated. Dallman Ross |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges with dependencies
Hi Dallman
Not sure whether this gets you any further, but I use a broadly similar technique for defining ranges. I have a Name Arng = Sheet1!$A$1:$Z$10000 or some suitably large size to cope with the maximum range of data I am going to be dealing with. I have lr (for Lastrow) using COUNTA() as you do to determine the last used row of the sheet. I have a List set up, which is a list of all of my column headings from the sheet Then for each named range, it is the same formula, just changing the column Name e.g. Analysis = INDEX(ARng,1,MATCH("Analysis",List,0)):INDEX(Arng, lr,MATCH("Analysis",List,0)) Inv Date = INDEX(ARng,1,MATCH("Inv Date",List,0)):INDEX(Arng,lr,MATCH("Inv Date",List,0)) It becomes a simple quick Copy and Paste of Defined Name into the one formula to create each range. -- Regards Roger Govier "Dallman Ross" <dman@localhost. wrote in message ... I've started converting some charts I have whose data gets updated very frequently so that the charts update their data ranges automatically. For example, there is a method outlined he http://www.j-walk.com/ss/excel/usertips/tip053.htm That uses the "Name" feature (Insert / Name / Define) to set up ranges that rely on COUNTA to see how long the range is. Cool. I'm interested in simplifying it further. I have many charts with many series. They all rely on the same (dynamic) length in the data range. So it seems kind of silly to define a bunch of different names, all of which are very similar except for the column number. I now have stuff like this: Name Value Unrealized.Rows =COUNTA('Current CSV'!$A:$A)-2 Unrealized.DaysHeld.Range ='SheetU'!$N$2:INDEX('SheetU'!$N:$N,Unrealized.Row s) Unrealized.Quote.Range ='SheetU'!$J$2:INDEX('SheetU'!$J:$J,Unrealized.Row s) Unrealized.GLRunning.Range ='SheetU'!$M$2:INDEX('SheetU'!$M:$M,Unrealized.Row s) Unrealized.PPS.Range ='SheetU'!$F$2:INDEX('SheetU'!$F:$F,Unrealized.Row s) Then, in the chart, I have series such as one called "Gain/Loss Running Tally" whose range is: ='SheetU'!Unrealized.GLRunning.Range and so on. Works fine. I want to have a Name or a user-defined function that lets me state the column number when I invoke the function. Then I could just have one repeating entry in the chart for each series. The "Gain/Loss Running Tally", "Days Held", and "Price Paid" series in my chart could just be, respectively, ='SheetU'!Unrealized.Range("M") ='SheetU'!Unrealized.Range("N") ='SheetU'!Unrealized.Range("F") where the arguments are the column numbers. Then I wouldn't need eight or so names for each type of chart I'm producing. I don't know how to create such a user-defined function, however! Help would be very much appreciated. Dallman Ross |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges with dependencies
In , Roger Govier
spake thusly: Thanks, Roger. I actually also did think about using MATCH with the column names to automate the named-range formulas further, similarly to what you have done. I didn't implement it because (a) I would have needed an hour or so of trial and error to get it to work (and anyway, I just started all this today); and (b) it still requires a bunch of similar named ranges rather than one that could self-populate based on the argument. Hmm. Nevertheless, I find your method pretty slick, and also feel somewhat vindicated to find that I chose an approach that turns out to be similar to yours. Obtw: what's the advantage to having "List" with all the column headers rather than matching on Row 1 itself? Dallman =============== Hi Dallman Not sure whether this gets you any further, but I use a broadly similar technique for defining ranges. I have a Name Arng = Sheet1!$A$1:$Z$10000 or some suitably large size to cope with the maximum range of data I am going to be dealing with. I have lr (for Lastrow) using COUNTA() as you do to determine the last used row of the sheet. I have a List set up, which is a list of all of my column headings from the sheet Then for each named range, it is the same formula, just changing the column Name e.g. Analysis = INDEX(ARng,1,MATCH("Analysis",List,0)):INDEX(Arng, lr,MATCH("Analysis",List,0)) Inv Date = INDEX(ARng,1,MATCH("Inv Date",List,0)):INDEX(Arng,lr,MATCH("Inv Date",List,0)) It becomes a simple quick Copy and Paste of Defined Name into the one formula to create each range. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges with dependencies
Hi Dallman
Obtw: what's the advantage to having "List" with all the column headers rather than matching on Row 1 itself? None really, except that I don't usually have my headers on Row 1 and, I much prefer using named ranges to row or column references. I nearly always leave 5 rows free at the top of any sheet I am setting up, in case I want to use them for other things e.g. placing subtotals there. I then hide any rows that I am not using. In reality my Arng's are $A$6:$Z$10000 but without going on to explain all of the above, it was easier to answer using $A$1 -- Regards Roger Govier "Dallman Ross" <dman@localhost. wrote in message ... In , Roger Govier spake thusly: Thanks, Roger. I actually also did think about using MATCH with the column names to automate the named-range formulas further, similarly to what you have done. I didn't implement it because (a) I would have needed an hour or so of trial and error to get it to work (and anyway, I just started all this today); and (b) it still requires a bunch of similar named ranges rather than one that could self-populate based on the argument. Hmm. Nevertheless, I find your method pretty slick, and also feel somewhat vindicated to find that I chose an approach that turns out to be similar to yours. Obtw: what's the advantage to having "List" with all the column headers rather than matching on Row 1 itself? Dallman =============== Hi Dallman Not sure whether this gets you any further, but I use a broadly similar technique for defining ranges. I have a Name Arng = Sheet1!$A$1:$Z$10000 or some suitably large size to cope with the maximum range of data I am going to be dealing with. I have lr (for Lastrow) using COUNTA() as you do to determine the last used row of the sheet. I have a List set up, which is a list of all of my column headings from the sheet Then for each named range, it is the same formula, just changing the column Name e.g. Analysis = INDEX(ARng,1,MATCH("Analysis",List,0)):INDEX(Arng, lr,MATCH("Analysis",List,0)) Inv Date = INDEX(ARng,1,MATCH("Inv Date",List,0)):INDEX(Arng,lr,MATCH("Inv Date",List,0)) It becomes a simple quick Copy and Paste of Defined Name into the one formula to create each range. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named ranges with dependencies
In , Roger Govier
spake thusly: Hi Dallman Obtw: what's the advantage to having "List" with all the column headers rather than matching on Row 1 itself? None really, except that I don't usually have my headers on Row 1 and, I much prefer using named ranges to row or column references. I nearly always leave 5 rows free at the top of any sheet I am setting up, in case I want to use them for other things e.g. placing subtotals there. Okay, I'm with you. Thanks very much for the good explanations and help! Dallman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mass Creation of Named Ranges? | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |