R
robbbo
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have finally figured out how to dynamically scale a chart that links to a table that can change by have the trailing cells empty.
The Table has two rows and 16 columns C6:R6 are dates and C7:R7 are numbers. When any of the cells at the end of the table are blank such as Q6 and R6 being blank I want the chart to only plot 14 data points. I should add C6:R7 are all formulas that reference another place in the work book.
That I did is define names Where the name "NBCL" is =OFFSET(Sheet1!$C$7,0,0,1,COUNT(Sheet1!$C$7:$R$7))
and "DATUP" is =OFFSET(Sheet1!$C$6,0,0,1,COUNT(Sheet1!$C$6:$R$6))
I then write the Series equations as:
=SERIES(,'Scaling experiment.xlsx'!DATUP,'Scaling experiment.xlsx'!NBCL,1)
It works great except if all the data is removed from the table and then put back the Chart will not plot and I get the message "Your formula contains an invalid external reference to a worksheet". When I save the workbook, close it, and reopen it the chart now plots correctly again.
Why series equation never changes, so why does it do this?
The Table has two rows and 16 columns C6:R6 are dates and C7:R7 are numbers. When any of the cells at the end of the table are blank such as Q6 and R6 being blank I want the chart to only plot 14 data points. I should add C6:R7 are all formulas that reference another place in the work book.
That I did is define names Where the name "NBCL" is =OFFSET(Sheet1!$C$7,0,0,1,COUNT(Sheet1!$C$7:$R$7))
and "DATUP" is =OFFSET(Sheet1!$C$6,0,0,1,COUNT(Sheet1!$C$6:$R$6))
I then write the Series equations as:
=SERIES(,'Scaling experiment.xlsx'!DATUP,'Scaling experiment.xlsx'!NBCL,1)
It works great except if all the data is removed from the table and then put back the Chart will not plot and I get the message "Your formula contains an invalid external reference to a worksheet". When I save the workbook, close it, and reopen it the chart now plots correctly again.
Why series equation never changes, so why does it do this?