Jul 11 2019 08:13 AM
As you can see above, I have data of 7000 rows in one column and I want to see graphical change for every 30 odd blocks of data. is there any easy way to insert any button or anything to move the block down when I press the arrow so that I can see the graphical changes?
Jul 11 2019 07:56 PM
Yes, that can be done. It's one of the core techniques of dynamic charts in Excel.
What you need:
For 1: You may want to use a Spin Button or a Scroll Bar from the Developer ribbon > Insert dialog. If you use a Scroll bar, set it up so that a page change equals 30, the minimum is 1 and the maximum is the number of rows in your data set. The Scroll Bar writes its value into a spreadsheet cell. Give that cell a range name, like "SelectedBlock" so it is easier to read in formulas. (you could also let the user type in the start row of the data block into that cell manually)
For 2: Create two named ranges with these names and formula references. (Assuming your data starts in A6
graphX =OFFSET(Sheet1!$A$6,SelectedBlock-1,0,30,1)
graphY =OFFSET(graphX,0,1)
For 3: Create a new chart or use an existing chart to plug in the names in the series dialog. When you enter the names, you need to prepend it with the sheet name, like in the screenshot.
When the user clicks on the Scroll Bar slider, the value in the SelectedBlock cell will change. This will cause a recalculation of the graphX and graphY range names and the chart will adjust.
Jul 22 2019 06:14 AM
Thank you very much for responding to my Query. can you please add some pictures to the point 1. in your description? I am not able to figure out exactly how to setup scroll bars or spin buttons to my data.@Ingeborg Hawighorst
Jul 24 2019 10:00 PM
@Santhosh750 First, you need to enable the Developer ribbon. On that ribbon you can find the "insert" drop-down, which has two sections, Form Controls and ActiveX Controls. Use only the Form Controls.
After you have inserted a control like a spin button or scroll bar, you can right-click and format the control to get to all the settings.