| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extend values of a chart series
Hello,
Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
I think what you'll need is a dynamic range in your chart. You can see how
the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
I'm doing the 1st part in that link where I'm putting in the "Names in
Workbook" box. It keeps telling me my name is not vaild, using "Sheet5!Y2008", but if I remove the exclamation point it will add it but otherwise it throws an error. "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
It is sheet5 but in the tab in excel that says the sheet name at the bottom
of the window is Aug 08' or Jul 08' or Dec 08'... "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
Ok, I got it ti work it was two single quotes not a double quote.
But Column E has formulas in the cells all the way down(=C'row#'/G'row#'), and when the data is entered into column C and G it calculates that cell in E obviously. Is there a way around this or should I delete the formuals and then add them in programatically when the data is being programatically entered into the excel file? **I can't spell programactically, lol "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
If you are using a worksheet level (local) name, prefix the name with the
tab name (not the codename) and exclamation mark. With more typical workbook level (global) names best prefix with the workbook name and exclamation mark. With both types you might need to embrace the prefix (before the !) with apostrophes. In code, best always include them just in case. Regards, Peter T "matt" wrote in message ... It is sheet5 but in the tab in excel that says the sheet name at the bottom of the window is Aug 08' or Jul 08' or Dec 08'... "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
Sorry I didn't explain that too well I was asking about a way around that
because the table thinks that it is data in those cells when really it's just an error from Excel saying you can't divide by 0 --- Displayed is -- #DIV/0! "matt" wrote: Ok, I got it ti work it was two single quotes not a double quote. But Column E has formulas in the cells all the way down(=C'row#'/G'row#'), and when the data is entered into column C and G it calculates that cell in E obviously. Is there a way around this or should I delete the formuals and then add them in programatically when the data is being programatically entered into the excel file? **I can't spell programactically, lol "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
If you are adding a named range specific to a worksheet named "My Sheet",
enter this for the name 'My Sheet'!myName. You need the single quotes if the sheet name has a space in it. If the sheetname is mySheet use mySheet!myName -- HTH, Barb Reinhardt "matt" wrote: It is sheet5 but in the tab in excel that says the sheet name at the bottom of the window is Aug 08' or Jul 08' or Dec 08'... "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
Yea I needed ... 'Aug ''08'!myName ... just before the 08 its two
single quotes I was doin one double quote. And when you can, can you look at the next question in this post about the formula "Barb Reinhardt" wrote: If you are adding a named range specific to a worksheet named "My Sheet", enter this for the name 'My Sheet'!myName. You need the single quotes if the sheet name has a space in it. If the sheetname is mySheet use mySheet!myName -- HTH, Barb Reinhardt "matt" wrote: It is sheet5 but in the tab in excel that says the sheet name at the bottom of the window is Aug 08' or Jul 08' or Dec 08'... "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
I'm not sure exactly what you're asking, but if you're getting a DIV/0 error
and you don't want to see it, you can use something like this =IF(ISERROR(YourCalc),NA(),YourCalc) -- HTH, Barb Reinhardt "matt" wrote: Sorry I didn't explain that too well I was asking about a way around that because the table thinks that it is data in those cells when really it's just an error from Excel saying you can't divide by 0 --- Displayed is -- #DIV/0! "matt" wrote: Ok, I got it ti work it was two single quotes not a double quote. But Column E has formulas in the cells all the way down(=C'row#'/G'row#'), and when the data is entered into column C and G it calculates that cell in E obviously. Is there a way around this or should I delete the formuals and then add them in programatically when the data is being programatically entered into the excel file? **I can't spell programactically, lol "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend values of a chart series
What I ment was that the formulas are already in all the cells in column E
even the ones that dont have data to do the calculations(There is a row for each day of the month). So those cells aren't blank they have the DIV/0 showing in the cell, so the formula I used from that website is looking in that column to see if there is data in those cells and it thinks there is because of the DIV/0 and so it extends the dataseries to cover all those cells. Sorry about the confusion... Make sense? So would that formula you just gave me fix that problem and make the cell look empty instead of having the DIV/0 in it? Thanks You... "Barb Reinhardt" wrote: I'm not sure exactly what you're asking, but if you're getting a DIV/0 error and you don't want to see it, you can use something like this =IF(ISERROR(YourCalc),NA(),YourCalc) -- HTH, Barb Reinhardt "matt" wrote: Sorry I didn't explain that too well I was asking about a way around that because the table thinks that it is data in those cells when really it's just an error from Excel saying you can't divide by 0 --- Displayed is -- #DIV/0! "matt" wrote: Ok, I got it ti work it was two single quotes not a double quote. But Column E has formulas in the cells all the way down(=C'row#'/G'row#'), and when the data is entered into column C and G it calculates that cell in E obviously. Is there a way around this or should I delete the formuals and then add them in programatically when the data is being programatically entered into the excel file? **I can't spell programactically, lol "Barb Reinhardt" wrote: I think what you'll need is a dynamic range in your chart. You can see how the technique is used here http://www.peltiertech.com/Excel/Cha...umnChart1.html -- HTH, Barb Reinhardt "matt" wrote: Hello, Ok, so I have some code that opens a seperate Excel file and takes some information from that and puts it into another Excel file that I have call "Sales.xls". In "Sales.xls" there is a chart, i think it is just called "Chart 1". Well the info that is pulled from the one file is put into a column that is used for the data for the chart. How can I programatically extend that series to the most recent data, without having to manually click the series in the chart and then having to extend the box to encompass the new data? I tried recording a macro doing that just to see what the out-put would be, and try to mimic that to what I need. But no such luck. Here is the CODE from the "Recorded Macro": __________________________________________________ _________ ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).Values = "='Jul ''08'!R4C3:R33C3" ActiveChart.SeriesCollection(3).Select ActiveChart.SeriesCollection(3).Values = "='Jul ''08'!R3C5:R33C5" __________________________________________________ _________ ---This is for two different series' in the same chart. The sheet is called " Jul '08 ", but that will change with the month. I have one sheet for each month so I guess ActiveSheet would be preferred... Thanks, Matt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In Office 2007 can't see chart series values unless chart unprotec | Charts and Charting in Excel | |||
| Extend series | Excel Worksheet Functions | |||
| Excel should not automatically extend series or fill values | Excel Discussion (Misc queries) | |||
| how do i extend the series of a portion of a formula? | Excel Worksheet Functions | |||
| Using SeriesCollection.Extend for a specific Chart Data Series | Excel Programming | |||
Linear Mode
