long values in plotting graph

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I shorten this series of value when plotting the graph? It's over 255 characters.

=inventory turnover!$E$15,inventory turnover!$H$15,inventory turnover!$K$15,inventory turnover!$N$15,inventory turnover!$Q$15,inventory turnover!$T$15,inventory turnover!$W$15,inventory turnover!$Z$15,inventory turnover!$AC$15,inventory turnover!$AF$15,inventory turnover!$AI$15, inventory turnover!$AL$15
 
1. Change the worksheet named 'inventory turnover' to
something shorter like 'inv trn'

2. Create a new sheet which contains the data you need for
your graph and create the graph from this sheet.

-----Original Message-----
How can I shorten this series of value when plotting the
Click to expand...
graph? It's over 255 characters.
=inventory turnover!$E$15,inventory turnover!
Click to expand...
$H$15,inventory turnover!$K$15,inventory turnover!
$N$15,inventory turnover!$Q$15,inventory turnover!
$T$15,inventory turnover!$W$15,inventory turnover!
$Z$15,inventory turnover!$AC$15,inventory turnover!
$AF$15,inventory turnover!$AI$15, inventory turnover!$AL$15
 
I found that this formula is not too long:
=SERIES(,,('inventory turnover'!R15C5,'inventor
turnover'!R15C5,'inventory turnover'!R15C14,'inventor
turnover'!R15C17,'inventory turnover'!R15C20,'inventor
turnover'!R15C23,'inventory turnover'!R15C26,'inventor
turnover'!R15C29,'inventory turnover'!R15C32,'inventor
turnover'!R15C35,'inventory turnover'!R15C38),1)

If that doesn’t work, why not give your data sheet a shorter name?

Or, you could try replicating the values you want into adjacent cells
and use that range as the data range for your series, eg in a new shee
put these formulae in successive cells:
='inventory turnover'!$E$15
='inventory turnover'!$H$15
='inventory turnover'!$K$15
='inventory turnover'!$N$15
='inventory turnover'!$Q$15
='inventory turnover'!$T$15
='inventory turnover'!$W$15
='inventory turnover'!$z$15
='inventory turnover'!$AC$15
='inventory turnover'!$AF$15
='inventory turnover'!$AI$15
='inventory turnover'!$AL$15
then use that range as your source

Finally, if you don’t need the chart series data to be linked to th
data in the sheet, you can insert the values as an array. To spee
things up, try running this macro:

Sub chart_series_source_values()
Dim chart_values As Variant
chart_values = Array(1)
source_cells = Array("$E$15", "$N$15", "$Q$15", "$T$15", "$W$15"
"$Z$15", "$AC$15", "$AF$15", "$AI$15", "$AL$15") 'addresses of th
cells with the values in
sheet_name = "inventory turnover"

For n = LBound(source_cells) To UBound(source_cells)
ReDim Preserve chart_values(n)
chart_values(n) = Sheets(sheet_name).Range(source_cells(n)).Value
Next

ActiveChart.SeriesCollection(1).Values = chart_values
End Su
 
Back
Top