Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
I have a dynamic line chart with 12 data series that each contains weekly
data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
It sounds like you are plotting a LINE chart. Can you change it to a
scatter chart? "sahafi" wrote: I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Yes, i'm using a line type. Using scatter did not help either.
Thanks. -- when u change the way u look @ things, the things u look at change. "Barb Reinhardt" wrote: It sounds like you are plotting a LINE chart. Can you change it to a scatter chart? "sahafi" wrote: I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
It would help to see your formulas.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Jon,
First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Here's the formula i'm trying to use to help plot only the data and
ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Thanks Jon for the help. I will give it a try and let you know. One thing
though, why do I need to create a range for each series on the 'Y' axis? My chart is designed to show one series at a time when the user select an item from the drop down list, colum 'O' gets populated with values for that particular item. The next time a user select a different item the values for that item gets populated on the same column 'O'. So i'm thinking one range for 'Y' (to use column 'O' data), and one for 'X', instead of 12 names for the 'Y' axis... agree? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Jon, while your method does work as is, it doesn't fit my design. Meaning,
your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your
linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Yep, It worked this time. Jon, you are a true Excel MVP
Many thanks for the help. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Just noticed that when selecting an item from the list the chart plots 2
lines on top of each other. When I hover the mouse over the tick mark, it will say series2 and on the line will say series1 and that for all 12 items even though I have series names from series1 to series12! When I click on the line the formula will say: =SERIES("Series2",,Model!ChartData2,13) and that for all 12 items as well.. exactly the same forumla. ChartData is my name range for 'Y' and weeks is my name range for 'X'. I have changed all 12 formulas for 'Y' series as: =OFFSET(weeks,0,Model!$S$64) S64 is my link cell. Also when collapsing the textbox for the 'Y' value under source data, all 12 lines will point to one same column, but the values on the chart are actually correct for all 12 items!! I probably messed up somewhere... any idea? Thanks. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
I thought you only wanted one series in the chart. Delete the duplicates.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Just noticed that when selecting an item from the list the chart plots 2 lines on top of each other. When I hover the mouse over the tick mark, it will say series2 and on the line will say series1 and that for all 12 items even though I have series names from series1 to series12! When I click on the line the formula will say: =SERIES("Series2",,Model!ChartData2,13) and that for all 12 items as well.. exactly the same forumla. ChartData is my name range for 'Y' and weeks is my name range for 'X'. I have changed all 12 formulas for 'Y' series as: =OFFSET(weeks,0,Model!$S$64) S64 is my link cell. Also when collapsing the textbox for the 'Y' value under source data, all 12 lines will point to one same column, but the values on the chart are actually correct for all 12 items!! I probably messed up somewhere... any idea? Thanks. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Yes, I do need to show only one line. Every item I select will show as two
lines one behind the other, and all 12 items will show one line as series1 while the other as series2. But I have named my series as series1, series2, series3,...series12. None of the items will show series3 - series12. when you asked me to modify the formula to: =OFFSET(weeks,0,'Model'!$S$64) you meant for all 12 'Y' values, correct? -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: I thought you only wanted one series in the chart. Delete the duplicates. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Just noticed that when selecting an item from the list the chart plots 2 lines on top of each other. When I hover the mouse over the tick mark, it will say series2 and on the line will say series1 and that for all 12 items even though I have series names from series1 to series12! When I click on the line the formula will say: =SERIES("Series2",,Model!ChartData2,13) and that for all 12 items as well.. exactly the same forumla. ChartData is my name range for 'Y' and weeks is my name range for 'X'. I have changed all 12 formulas for 'Y' series as: =OFFSET(weeks,0,Model!$S$64) S64 is my link cell. Also when collapsing the textbox for the 'Y' value under source data, all 12 lines will point to one same column, but the values on the chart are actually correct for all 12 items!! I probably messed up somewhere... any idea? Thanks. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic chart, OFFSET, #N/A Help
Never mind. Got it.
Once again thank you -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: I thought you only wanted one series in the chart. Delete the duplicates. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Just noticed that when selecting an item from the list the chart plots 2 lines on top of each other. When I hover the mouse over the tick mark, it will say series2 and on the line will say series1 and that for all 12 items even though I have series names from series1 to series12! When I click on the line the formula will say: =SERIES("Series2",,Model!ChartData2,13) and that for all 12 items as well.. exactly the same forumla. ChartData is my name range for 'Y' and weeks is my name range for 'X'. I have changed all 12 formulas for 'Y' series as: =OFFSET(weeks,0,Model!$S$64) S64 is my link cell. Also when collapsing the textbox for the 'Y' value under source data, all 12 lines will point to one same column, but the values on the chart are actually correct for all 12 items!! I probably messed up somewhere... any idea? Thanks. -- If u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Your dropdown box has 12 items, so assuming a Forms toolbar dropdown, your linked cell has a value from 1 to 12. Assuming the X is in one column, the 12 Ys in the next 12 columns, and the linked cell to the dropdown is cell J1, change your Refers To formula for Y to this: Name: ChartValues RefersTo: =OFFSET(ChartCategories,0,'Model'!$J$1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, while your method does work as is, it doesn't fit my design. Meaning, your explanation of creating 12 data series will plot 12 line on the chart, which is not what i'm after. Remember I mentioned I have a combobox with dropdown list from which a user will select one item out of the 12, and they should see a single line for that item. Showing 12 lines week by week makes the chart too busy, besides, i'm really ploting products growth %. There's no growth relationship between the products themselves, so no sense plotting all of them at once. Is there a way to accomplish this dynamically and having the dropdown list working at the same time? Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: Here's the formula i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1) I hope this is =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) and that B1:B65 are all filled. Is this the refers-to formula for a named range? You should have one range for X and one for Y for each series in the chart (though the X can be used by multiple series). Named range for X values: Name: ChartCategories RefersTo: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1) Better: =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1) Named range for first set of Y values: Name: ChartValues1 RefersTo: =OFFSET(ChartCategories,0,1) Named range for second set of Y values: Name: ChartValues2 RefersTo: =OFFSET(ChartCategories,0,2) etc. Use ='Model'!ChartCategories ='Model'!ChartValues1 for the X values and Y values for the first series, ='Model'!ChartCategories ='Model'!ChartValues2 for the X values and Y values for the second series, etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... Jon, First my list is populated from another report list on the same sheet. My chart list is as follow: Weeks on column B (B66:B117) while my heading/labels start from C65 to N65. I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link cell. On C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117 this formula basically looks the above report list and populate the data while substitute any zero values with #N/A. Up to this point my chart is fine and the combobox is working fine. Currently my list is showing actual data up to week 16 and #N/A thereafter. Here's the formual i'm trying to use to help plot only the data and ignores #N/A which currently it didn't =OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have similar formula based on the values on column O, as my 'Y' axis values. Model is my sheet where the report list chart list and the chart reside. Thanks. -- when u change the way u look @ things, the things u look at change. "Jon Peltier" wrote: It would help to see your formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "sahafi" wrote in message ... I have a dynamic line chart with 12 data series that each contains weekly data for 52 weeks, and set up as a combo box chart. Currently I have data for only 16 weeks, while the rest of the year is howing zero values. I changed the zero to #N/A to avoid plotting that on the graph, which worked fine, but the issue is the X axis. I have tried 2 options but neither produced what I needed. * I have used a formula to show the weeks as N/A if no data available on the next column. ** I used the OFFSET formula to creat a range, but both options plotted the #N/A on the X axis. How can I incorportate a range successfully in another OFFSET formula (combo box with dropdown list) to plot only the cells with data. Any help is greatly appreciated. -- when u change the way u look @ things, the things u look at change. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic reference in OFFSET function | Excel Worksheet Functions | |||
Dynamic Ranges and Offset | Excel Worksheet Functions | |||
Dynamic Charts Offset | Charts and Charting in Excel | |||
dynamic offset | Excel Discussion (Misc queries) | |||
dynamic range / offset | Excel Worksheet Functions |