Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
I have a dilemma I have a spreadsheet that only has a chart in it, that was
linked to another spreadsheet. The other spreadsheet has disappeared (HD died and data was not recoverable and no backups were made). I can see that the chart still has all of the correct data points in it, because I cna hover the mouse over the line and it will tell me the values. Is there a way to extract the data from the chart so I can rebuild my original spreadsheet. The amount of data is 4 daily readings of 4 different values from the Financial markets over thelast 4 years, so it is a lot of data. Any suggestions would be greatly appreciated. Clarke |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
Click on one of the series in the chart, you will see the series formula in
the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
Click on one of the series in the chart, you will see the series formula in
the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
Thanks Ed this worked like a treat.
Clarke "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
Thanks Ed, I did some analysis last night and it seems to only extract a
certain amount. It seems to have a predefined limit int he number of characters it will display in the formula bar. It converts all of the dates to number format, ie 36789, which is fine, and when you format the cell in excel where the new data is going, it gives you the data correctly. However it does not bring in all of the data points. It only converts the fist 300-400, I have over 1000 data points in each line on the graph. Any ideas. "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
I suspected that would be an issue. Here's a quick and dirty sub for
extracting a chart's data: Sub ExtractChartData() Dim iSrs As Long Dim cht As Chart Dim srs As Series Dim ws As Worksheet Set cht = ActiveChart Set ws = Worksheets.Add For iSrs = 1 To cht.SeriesCollection.Count Set srs = cht.SeriesCollection(iSrs) ws.Cells(1, 2 * iSrs).Value = srs.Name ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.XValues) ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.Values) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "CLarke" wrote in message ... Thanks Ed, I did some analysis last night and it seems to only extract a certain amount. It seems to have a predefined limit int he number of characters it will display in the formula bar. It converts all of the dates to number format, ie 36789, which is fine, and when you format the cell in excel where the new data is going, it gives you the data correctly. However it does not bring in all of the data points. It only converts the fist 300-400, I have over 1000 data points in each line on the graph. Any ideas. "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
In addition to Jon's macro, Microsoft provides a Knowledge Base article with
another snippet of code to extract data from a chart. http://support.microsoft.com/kb/300643 -- John Mansfield http://cellmatrix.net "Jon Peltier" wrote: I suspected that would be an issue. Here's a quick and dirty sub for extracting a chart's data: Sub ExtractChartData() Dim iSrs As Long Dim cht As Chart Dim srs As Series Dim ws As Worksheet Set cht = ActiveChart Set ws = Worksheets.Add For iSrs = 1 To cht.SeriesCollection.Count Set srs = cht.SeriesCollection(iSrs) ws.Cells(1, 2 * iSrs).Value = srs.Name ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.XValues) ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.Values) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "CLarke" wrote in message ... Thanks Ed, I did some analysis last night and it seems to only extract a certain amount. It seems to have a predefined limit int he number of characters it will display in the formula bar. It converts all of the dates to number format, ie 36789, which is fine, and when you format the cell in excel where the new data is going, it gives you the data correctly. However it does not bring in all of the data points. It only converts the fist 300-400, I have over 1000 data points in each line on the graph. Any ideas. "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
The Microsoft macro assumes all series in the chart share the same X values.
Mine assumes that each series has distinct X values. If theirs is wrong you lose data. If mine is wrong you get duplicated data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John Mansfield" wrote in message ... In addition to Jon's macro, Microsoft provides a Knowledge Base article with another snippet of code to extract data from a chart. http://support.microsoft.com/kb/300643 -- John Mansfield http://cellmatrix.net "Jon Peltier" wrote: I suspected that would be an issue. Here's a quick and dirty sub for extracting a chart's data: Sub ExtractChartData() Dim iSrs As Long Dim cht As Chart Dim srs As Series Dim ws As Worksheet Set cht = ActiveChart Set ws = Worksheets.Add For iSrs = 1 To cht.SeriesCollection.Count Set srs = cht.SeriesCollection(iSrs) ws.Cells(1, 2 * iSrs).Value = srs.Name ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.XValues) ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.Values) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "CLarke" wrote in message ... Thanks Ed, I did some analysis last night and it seems to only extract a certain amount. It seems to have a predefined limit int he number of characters it will display in the formula bar. It converts all of the dates to number format, ie 36789, which is fine, and when you format the cell in excel where the new data is going, it gives you the data correctly. However it does not bring in all of the data points. It only converts the fist 300-400, I have over 1000 data points in each line on the graph. Any ideas. "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Chart Data Recovery
Thanks a lot guys you made a 75 year old man very very grateful.
"Jon Peltier" wrote: I suspected that would be an issue. Here's a quick and dirty sub for extracting a chart's data: Sub ExtractChartData() Dim iSrs As Long Dim cht As Chart Dim srs As Series Dim ws As Worksheet Set cht = ActiveChart Set ws = Worksheets.Add For iSrs = 1 To cht.SeriesCollection.Count Set srs = cht.SeriesCollection(iSrs) ws.Cells(1, 2 * iSrs).Value = srs.Name ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.XValues) ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.Values) Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "CLarke" wrote in message ... Thanks Ed, I did some analysis last night and it seems to only extract a certain amount. It seems to have a predefined limit int he number of characters it will display in the formula bar. It converts all of the dates to number format, ie 36789, which is fine, and when you format the cell in excel where the new data is going, it gives you the data correctly. However it does not bring in all of the data points. It only converts the fist 300-400, I have over 1000 data points in each line on the graph. Any ideas. "Ed Ferrero" wrote: Click on one of the series in the chart, you will see the series formula in the formula bar at the top of the screen. It will look something like this; =SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1) Then click on the formula in the formula bar, and press F9. The series formula will now show values. Like this; =SERIES("Series name",{1,2,3},{4,5,6},1) Select the whole formula (or just the bits between curly brackets) and copy it to a worksheet cell. Use Data - Text to Columns to parse the values into something you can use. Ed Ferrero www.edferrero.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data recovery from WORM_NYXEM.E affeted excell files--very urgent | Excel Discussion (Misc queries) | |||
excel data recovery | Excel Discussion (Misc queries) | |||
Professional Data Recovery Software Development Help | Excel Discussion (Misc queries) | |||
Auto recovery | Excel Discussion (Misc queries) | |||
Auto Recovery | Excel Discussion (Misc queries) |