Chart Source Data Formats

  • Thread starter Thread starter dilworth
  • Start date Start date
D

dilworth

We currenlty use Excel Charts where the Source Data is linked to anothe
workbook. The Number Formats are not carried over correctly from thi
Workbook unless both Workbooks are open at the same time-however th
values are not affected by this and do not require the other Workboo
to be open.
We need to be able to E-Mail the Charts with the correct Number Format
without needing to open both Workbooks as the recipients will not hav
access to the Workbook containing the Source Data as this contain
other confidential information.
The version of Excel we are using is Microsoft Excel 2003.

Any help or suggestions much appreciated
 
By default, the charts link their number formats to the source data. If
they can't see the source data, the charts just use "General", which is
a major pain.

You can unlink the number formats of a chart's text elements. Double
click the element (an axis, perhaps). On the Number tab, uncheck the
little box that says Linked To Source.

If you have a lot of charts to do, you can always use a macro. I just
put together this pair. The first loops through all the charts (chart
sheets and embedded charts) in the active workbook, then calls the
second to unlink the axis labels and data labels.

Sub UntieNumberFormats()
Dim sh As Object
Dim cht As Chart
Dim chob As ChartObject
' loop through all chart sheets
For Each cht In ActiveWorkbook.Charts
Untie cht
Next
' loop through all chart objects on each sheet
For Each sh In ActiveWorkbook.Sheets
For Each chob In sh.ChartObjects
Untie chob.Chart
Next
Next
End Sub

Sub Untie(TheChart As Chart)
Dim i As Integer
Dim j As Integer
Dim srs As Series
' unlink axis tick labels
For i = 1 To 2
For j = 1 To 2
If TheChart.HasAxis(i, j) Then
TheChart.Axes(i, j).TickLabels. _
NumberFormatLinked = False
End If
Next
Next
' unlink series data labels
For Each srs In TheChart.SeriesCollection
srs.DataLabels.NumberFormatLinked = False
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks-I have checked this and the formats of the Axis are now correc
and the other woekbook does not need to be opened.

There is still a problem with the Data Tables which are linked to th
same Source Data in the other Workbook-I have tried to double-click th
Data Table but the only Options available to change are 'Pattern' an
'Font'.

Any suggestions appreciated
 
Is there any other way make the formatting match?
The reason I have the links active, instead of embedding, is to ensure
regular (monthly) updates carry over with minimal workload.

Tushar Mehta said:
Don't use the default data tables?

They have very limited formatting options. Instead, consider
simulating the effect a la
Custom Chart Table
http://www.tushar-mehta.com/excel/newsgroups/data_table/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Click to expand...
 
You can make a table in the worksheet next to the chart, and format it
however you want. This data can be linked to the same source data used
in the chart, and you can even use this data in the chart. If you can
get the charts to update with minimal fuss, you can also make this table
update just as easily.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Is there any other way make the formatting match?
The reason I have the links active, instead of embedding, is to ensure
regular (monthly) updates carry over with minimal workload.

:
Click to expand...
 
Back
Top