Problem with Including Named Range in a Chart: Excel 2007

[gmetric-f1]

Hello All,

I am pretty new here. I have one urgent problem to solve. We are new startup we have Excel 2007 with all of us.

I am using Named range in excel 2007 to use dynamic charting. In fact, since I am trying to automate, I have used INDIRECT within the OFFSET function--

Switch1_X =OFFSET(Analysis!$P$2,,,INDIRECT("COUNTA(Analysis!$O" & Analysis!$S$2 & ":Analysis!$O" & Analysis!$T$2),1)

Note that, 'Analysis'!$S$2 and Analysis'!$T$2 contain the beginning and ending cell numbers (arguments to COUNTA)

Now, I am trying to write in -

Series X Values: = Book2.xlsx!Switch1_X or
= Sheet1!Switch1_X

all these attempts end up in an error message --

"The formula in this worksheet contains one more invalid references"

or
" The formula contains an error"

Have spent 1.5 hours on it to figure out and no use.

Please do help me out ...

 

[pbornemeier]

Welcome to the Board!

I believe your named range should be formulated as follows:
Switch1_X =OFFSET(Analysis!$P$2,,,COUNTA(INDIRECT("Analysis!$O" & Analysis!$S$2 & ":$O" & Analysis!$T$2)),1)

Then this should work:
Series X Values: = Book2.xlsx!Switch1_X