|
Charts Range Setting
Hi All
I have a predefined chart that I want to set the range collection dynamically from VBA code. That works OK. A mixed bar-line chart for 12 months of the year on the X-Axis. My problem stems from variable number of values for each bar/line. I set the ranges as follows..... With chSales .SeriesCollection(1).Values = shSales.Range("B29:B" & BMax) .SeriesCollection(2).Values = shSales.Range("E29:E" & EMax) .SeriesCollection(3).Values = shSales.Range("J29:J" & BMax) .SeriesCollection(4).Values = shSales.Range("K29:K" & EMax) .SeriesCollection(1).XValues = shSales.Range("A29:A40") End With The BMax and EMax ranges are variable length from 1 to 12 values. The X values are fixed at 12. The problem arises in that the chart displays ONLY the X-values up to the B or E max vlaues whichever is grerater. I need it to show all 12 months regardless of how many values for the chart series there are, and DO not want the lines in the chart to drop to zero for the remainder of the chart. It must be possible? -- Cheers Nigel |
Charts Range Setting
Hi Tushar
Thanks for the advice. In my range I have a conditional formula that looks like this =IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a point shows on the chart . If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty #N/A in the cell which detracts from the table looks, but it does not show the point on the chart. Is there a way to create a blank cell AND not show the point if the cell has a formula? -- Cheers Nigel "Tushar Mehta" wrote in message om... Plot 29:40 for each column of interest and do either of the following: (1) Make sure the cells that do not contain data are truly empty (no formula, no zero-length string (""), no nothing. In addition, ensure that the Tools | Options... | Chart tab | 'Plot empty cells as' option is set to 'Not plotted'. (2) Replace the cells that should be empty with NA(). -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , nigel- says... Hi All I have a predefined chart that I want to set the range collection dynamically from VBA code. That works OK. A mixed bar-line chart for 12 months of the year on the X-Axis. My problem stems from variable number of values for each bar/line. I set the ranges as follows..... With chSales .SeriesCollection(1).Values = shSales.Range("B29:B" & BMax) .SeriesCollection(2).Values = shSales.Range("E29:E" & EMax) .SeriesCollection(3).Values = shSales.Range("J29:J" & BMax) .SeriesCollection(4).Values = shSales.Range("K29:K" & EMax) .SeriesCollection(1).XValues = shSales.Range("A29:A40") End With The BMax and EMax ranges are variable length from 1 to 12 values. The X values are fixed at 12. The problem arises in that the chart displays ONLY the X-values up to the B or E max vlaues whichever is grerater. I need it to show all 12 months regardless of how many values for the chart series there are, and DO not want the lines in the chart to drop to zero for the remainder of the chart. It must be possible? |
Charts Range Setting
I knew the question about aesthetics was coming. :)
Just hide the #N/A through conditional formatting. See http://groups- beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group: *Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e6 3 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , nigel- says... Hi Tushar Thanks for the advice. In my range I have a conditional formula that looks like this =IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a point shows on the chart . If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty #N/A in the cell which detracts from the table looks, but it does not show the point on the chart. Is there a way to create a blank cell AND not show the point if the cell has a formula? |
Charts Range Setting
You guessed it right!
I had be playing around with conditinal formatting - your tip makes it work. Pity I need to set each cell in the range one by one? Thanks again -- Cheers Nigel "Tushar Mehta" wrote in message om... I knew the question about aesthetics was coming. :) Just hide the #N/A through conditional formatting. See http://groups- beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr ead/e8029a50a033b833/3b17d65c02268e63?q=conditional+na+group: *Excel*+author:tushar&rnum=1&hl=en#3b17d65c02268e6 3 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , nigel- says... Hi Tushar Thanks for the advice. In my range I have a conditional formula that looks like this =IF(D20=0,"",D20*5) which results in the cell showing blank (empty(?)) but a point shows on the chart . If I change the formula to =IF(D20=0,NA(),D20*5) I end up with a nasty #N/A in the cell which detracts from the table looks, but it does not show the point on the chart. Is there a way to create a blank cell AND not show the point if the cell has a formula? |
Charts Range Setting
No, you don't have to do one cell at a time. Select all cells and set
the conditional formatting formula to use a relative address as in =ISNA(A1) rather than $A$1. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , nigel- says... You guessed it right! I had be playing around with conditinal formatting - your tip makes it work. Pity I need to set each cell in the range one by one? Thanks again |
Charts Range Setting
Brilliant!!
-- Cheers Nigel "Tushar Mehta" wrote in message om... No, you don't have to do one cell at a time. Select all cells and set the conditional formatting formula to use a relative address as in =ISNA(A1) rather than $A$1. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , nigel- says... You guessed it right! I had be playing around with conditinal formatting - your tip makes it work. Pity I need to set each cell in the range one by one? Thanks again |
| All times are GMT +1. The time now is 08:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com