Graphic but referencing the values range for Y axis in an externalcell (indirect reference)

[Diego Trujillano]

Hello

I have an excel sheet with three columns: dates on A, apple sales on
B, and orange sales on C.

Usually in order to insert a graphic you define for x axis range =
sheet1!$A$1:$A$12 and for y axis range = sheet1!$B$1:$B$12 (or range
sheet1!$c$1:$C$12 in case you want to represent orange sales).

But what I need is to write the range sheet1!$B$1:$B$12 in cell D1 and
tell to the graphic that the range for y axis is the content of cell
D1 (something similar to an indirect reference).

I have tried to write instead sheet1!$B$1:$B$12, INDIRECT (D1) where
D1 = sheet1!$B$1:$B$12 but excel doesn't accepted it.

Could someone help to do this? (I am not sure if this is possible)

Thank you very much

Diego.

 

[Jon Peltier]

Diego -

The only formulas allowed in the chart source data dialogs are link
formulas, either to cell addresses or to defined names. No functions are
allowed.

This can be handled in code:

ActiveChart.SeriesCollection(1).Values = _
Range(Worksheets("Sheet1").Range("D1").Value)

- Jon