[Solved] Excel Chart RANGED name: INVALID REFERENCE message

[Ria]

Hello all, NEED YOUR EXPERT HELP.

I am using Excel 2007. I have a chart and want to draw 2 lines with different data from 2 sheets in same workbook. Then I have scrollbar want to connect with this chart. For that I am creating Range names and using name range to create lines on chart. First line on chart created fine and scrollbar works fine.

I created 2 set of Range name with different data (different sheet in same workbook). When I create new line on chart and try to assign range names. It pops up message INVALID REFERENCE. I am struggling with it about a week now and burned my mind. Now I am asking for help from you experts. Please help

1st set of Named range: works fine and am able to connect with chart

=INDIRECT("Gamma!B"&WPTest!ScrollVal_1&":B"& WPTest!ScrollVal_1) èscope is sheet level

=INDIRECT("Gamma!C"&WPTest!ScrollVal_1&":C"& WPTest!ScrollVal_1) èscope is sheet level

2nd set of Named range: scope is workbook level= when i use following to create line on chart then it causes problem

=INDIRECT("Gamma!AL"&'StarSteerTest 5 V3 troubleshooting.xlsx'!ScrollVal_2&":AL"&'StarSteerTest 5 V3 troubleshooting.xlsx'!ScrollVal_2)

=INDIRECT("Gamma!AK"&'StarSteerTest 5 V3 troubleshooting.xlsx'!ScrollVal_2&":AK"&'StarSteerTest 5 V3 troubleshooting.xlsx'!ScrollVal_2)

I tried with scope level same for both sets of named ranges (sheet and workbook level) with alternate scope but when assign second set to create new line on char then ugly message pops up INVALID REFERENCE

 

[p45cal]

Please attach a workbook with this problem; it would take ages for someone to reproduce (probably wrongly) your set up.

 

[Ria]

Please attach a workbook with this problem; it would take ages for someone to reproduce (probably wrongly) your set up.

I am rephrasing the question. I solved that part but now stuck on next step.



I am using Excel 2007. I have a chart and want to draw 2 lines with different data from same sheet (sheet name: Gamma). Then I have scrollbar want to connect with this chart. For that I am creating Range names and using name range to create lines on chart because data and number of rows will vary as per need.

• Primary axis on chart for 2 lines
• Secondary axis on chart (Horizontal /lateral lines) they are ok for now

For primary axis: My name ranges are GR_On_Chart_1 = =WPTest!$AS$1

GR_On_Chart_2 = =WPTest!$AS$2

ScrollVal_1 = =WPTest!$AS$1

ScrollVal_2 = =WPTest!$AS$2







I created 2 set of Range name with different data (same sheet). I am struggling with it about a week now and burned my mind. Now I am asking for help from you experts. Please help

1st set of Named range: for first line on chart

=INDIRECT("Gamma!B"&WPTest!ScrollVal_1&":B"& WPTest!ScrollVal_1+ WPTest!GR_On_Chart_1)
=INDIRECT("Gamma!C"&WPTest!ScrollVal_1&":C"& WPTest!ScrollVal_1+ WPTest!GR_On_Chart_1)


2nd set of Named range: for 2nd line on chart
=INDIRECT("Gamma!AL"&WPTest!ScrollVal_1&":AL"& WPTest!ScrollVal_1+ WPTest!GR_On_Chart_1)

=INDIRECT("Gamma!AM"&WPTest!ScrollVal_1&":AM"& WPTest!ScrollVal_1+ WPTest!GR_On_Chart_1)

What I need help for:

a) when I scroll data of both lines should display accordingly, right now it is displaying in ugly manor. Not on the proper place. Either shrinking too much or expanding too much.

b) when data ends then scrollbar should not be able to scroll (right now if data ends and I scroll it keep scrolling and display on chart mess up while vertical axis on left side of the chart values going into 0.1, 0.2 etc.

If anyone can help and I can breathe. I am not expert and only copied these formulas from web and try to use but stuck and my job really need it working. I might be doing totally wrong. Please correct.



Attach is excel file. I deleted extra stuff to keep file size small.

 

[vletm]

#3 reply is same as below thread:

dynamic excel chart with scrollbar need help please

I am rephrasing the question. I solved that part but now stuck on next step. I am using Excel 2007. I have a chart and want to draw 2 lines with different data from same sheet (sheet name: Gamma). Then I have scrollbar want to connect with this chart. For that I am creating Range names and...

chandoo.org