Using named ranges to create dynamic charts in Excel 2003

Hi, Sir,

I managed to create a dynamic chart with named range in Excel. It worked well if the chart is in the same sheet with the named range. But if I move the chart into another sheet and tried to save the file, then a warning message pops up:

"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference"

Just don't know why this happened.

I checked the series() function which is: =SERIES(Data!$R$1,VS.xls!ROEYear,VS.xls!ROE,1) So here, ROEYear and ROE are the two names created.

Many thanks for your advice.

I found an article of Microsoft on how to use named range to make a dynamic chart. It is available here:

http://office.microsoft.com/en-us/ex...001109801.aspx

This approach used the COUNTA() function to make named range. But COUNTA() is not very useful in situations where we don't want those hidden null strings "" to be counted. I tried to use COUNTIF, COUNT and some other functions, but this always results in an error:

"A formula in this worksheet contains one or more invalid references. Verify that..."

If I use COUNTA() then the warning message will not show.

Can I ask if we have a way to do this, without counting those cells with null strings?