• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic excel chart with scrollbar need help please

R

Ria

Member
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.
 

Attachments

  • Excel Help.xlsx
    1,001.6 KB · Views: 2
Riya- It seems like you're dealing with dynamic data ranges and scrolling issues in Excel 2007. I can't directly view or manipulate files, but I can guide you through potential solutions:

a) Adjusting Data Display:
- Ensure that the range references in your named ranges are dynamic and adjust based on the scrollbar position.
- Use the `INDEX` function instead of `INDIRECT` to create dynamic ranges. For example,

Code: 
=INDEX(Gamma!$B$1:$B$100 ScrollVal_1):INDEX(Gamma!$B$1:$B$100, ScrollVal_1 + GR_On_Chart_1)

b) Limiting Scrollbar Range:
- You can use the `MAX` function to limit the scrollbar's maximum value based on your data range length. For example,
Code: 
=MAX(0, COUNT(Gamma!$B:$B) - WPTest!GR_On_Chart_1)
Remember to adjust these suggestions based on your specific data and requirements.
 
Monty said:
Riya- It seems like you're dealing with dynamic data ranges and scrolling issues in Excel 2007. I can't directly view or manipulate files, but I can guide you through potential solutions:

a) Adjusting Data Display:
- Ensure that the range references in your named ranges are dynamic and adjust based on the scrollbar position.
- Use the `INDEX` function instead of `INDIRECT` to create dynamic ranges. For example,

Code: 
=INDEX(Gamma!$B$1:$B$100 ScrollVal_1):INDEX(Gamma!$B$1:$B$100, ScrollVal_1 + GR_On_Chart_1)

b) Limiting Scrollbar Range:
- You can use the `MAX` function to limit the scrollbar's maximum value based on your data range length. For example,
Code: 
=MAX(0, COUNT(Gamma!$B:$B) - WPTest!GR_On_Chart_1)
Remember to adjust these suggestions based on your specific data and requirements.
Click to expand...
Thanks Monty. You are the first one who looked this issue deeply. Glad to hear.
Yes, I am dealing with dynamic charts through dynamic ranges. But having hard time to find the solution because I am not a good in excel programing. I switched from INDIRECT to OFFSET function. Scroll is adjusting dynamically.
1. I will try and test INDEX replacing OFFSET.
2. Using kind of MAX function to limit the scroll range and scroll range adjusts dynamically.
3. Not sure why it does not display on full plot area. Any thoughts would be appreciated.
 
Hello RIa

3 things to consider

1. Testing the `INDEX` function as a replacement for `OFFSET` is a good approach. It can provide a more efficient and stable solution for dynamic ranges.

2. Applying the `MAX` function to limit the scrollbar's maximum value based on your data range length is a sound strategy for preventing scrolling beyond the available data.

3. If the plot area isn't displaying fully, ensure that your chart's size and position are appropriate. Also, check if there are any overlapping elements or if the chart area is set to a fixed size. Adjusting these parameters might help resolve the display issue.
 
You must log in or register to reply here.
Back
Top