Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts
I am trying to create a dynamic line chart. The data in column A is dates
and the data in the header row (location) would be the series for the chart. The actual data is units. Problem is, more dates could be added as well as more locations. Is there any way to name the ranges to account for more or less data on both location and date? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts
Hi: I repeat my answer to another question here again. There is an elegant solution, called "dynamic named range", that works with the OFFSET command. Search the web with this jargon, or check out Jon Peltier's website; it contains links to many others where you find instructions how to do it. [http://peltiertech.com/Excel/Charts/...artLinks.html] HTH, Henk "bjw" wrote: I am trying to create a dynamic line chart. The data in column A is dates and the data in the header row (location) would be the series for the chart. The actual data is units. Problem is, more dates could be added as well as more locations. Is there any way to name the ranges to account for more or less data on both location and date? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts
I understand how to create named ranges and the OFFSET function. In order to
capture all the coulmns that could possibly be added to the report, i have to create named ranges for all of the blank columns, add them to the report and then when they are populated, they will be visible in the chart? Or is there another way to do it? Loc5 and Loc 6 and so on could be added at a later date and Date4 and Date 5 could be added...The dates i have under control...I am just wondering if i have to create 10-15 named ranges for the locations that have not been added yet? Loc2 Loc3 Loc4 Date1 Date2 Date3 "HEK" wrote: Hi: I repeat my answer to another question here again. There is an elegant solution, called "dynamic named range", that works with the OFFSET command. Search the web with this jargon, or check out Jon Peltier's website; it contains links to many others where you find instructions how to do it. [http://peltiertech.com/Excel/Charts/...artLinks.html] HTH, Henk "bjw" wrote: I am trying to create a dynamic line chart. The data in column A is dates and the data in the header row (location) would be the series for the chart. The actual data is units. Problem is, more dates could be added as well as more locations. Is there any way to name the ranges to account for more or less data on both location and date? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts
Create one dynamic name for Dates. Allow the user to select the column to
plot by placing the name into a cell. You can make this fancier and more robust later with a dropdown control. I'll assume your data is in A3:D10 (dates in A4:A10, "Loc2" to "Loc4" in B3:D3). The user for now types the column name he wants in B1 (e.g., "Loc3"). To know which column should be plotted, use MATCH(B1,B3:D3,0) in a new dynamic name definition: Name: RangeToPlot Refers To: =OFFSET(Dates,0,MATCH(B1,B3:D3,0)) Now set up your chart series to use Dates for X and RangeToPlot for Y. Having written the description above, I realize there's already an example using a combo box on my web site: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "bjw" wrote in message ... I understand how to create named ranges and the OFFSET function. In order to capture all the coulmns that could possibly be added to the report, i have to create named ranges for all of the blank columns, add them to the report and then when they are populated, they will be visible in the chart? Or is there another way to do it? Loc5 and Loc 6 and so on could be added at a later date and Date4 and Date 5 could be added...The dates i have under control...I am just wondering if i have to create 10-15 named ranges for the locations that have not been added yet? Loc2 Loc3 Loc4 Date1 Date2 Date3 "HEK" wrote: Hi: I repeat my answer to another question here again. There is an elegant solution, called "dynamic named range", that works with the OFFSET command. Search the web with this jargon, or check out Jon Peltier's website; it contains links to many others where you find instructions how to do it. [http://peltiertech.com/Excel/Charts/...artLinks.html] HTH, Henk "bjw" wrote: I am trying to create a dynamic line chart. The data in column A is dates and the data in the header row (location) would be the series for the chart. The actual data is units. Problem is, more dates could be added as well as more locations. Is there any way to name the ranges to account for more or less data on both location and date? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make dynamic charts more dynamic | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
dynamic charts | Charts and Charting in Excel | |||
Can't create dynamic charts | Charts and Charting in Excel | |||
Dynamic Charts | Excel Discussion (Misc queries) |