Hi All,
I need to create a dynamic named range to use in a chart from the following data but intersect each data point with a blank or zero. I'm unable to move each and every cells along so that you can select the range in the final data as the data comes from an external source.
example data (on sheet1)
<tbody>
</tbody>
resulting chart data for series1.
={23,0,234,0,532,0,43}
I thought maybe using concatenate in the named range array but perhaps this isn't right, for instance
=concatenate(Sheet1!B1:F1,",",0). This doesn't work anyway as it attempts to put the result value in each cell as a text item instead of keeping it as a value.
The reason I need this in the form of a dynamic range is so that the chart updates automatically and preferably without a macro / some VB running behind the scenes to update the ranges.
Hope someone can help.
Many thanks in advance
Rodp
I need to create a dynamic named range to use in a chart from the following data but intersect each data point with a blank or zero. I'm unable to move each and every cells along so that you can select the range in the final data as the data comes from an external source.
example data (on sheet1)
A | B | C | D | E | F | |
1 | Series1 | 23 | 234 | 532 | 12 | 43 |
<tbody>
</tbody>
resulting chart data for series1.
={23,0,234,0,532,0,43}
I thought maybe using concatenate in the named range array but perhaps this isn't right, for instance
=concatenate(Sheet1!B1:F1,",",0). This doesn't work anyway as it attempts to put the result value in each cell as a text item instead of keeping it as a value.
The reason I need this in the form of a dynamic range is so that the chart updates automatically and preferably without a macro / some VB running behind the scenes to update the ranges.
Hope someone can help.
Many thanks in advance
Rodp