in the excel i have data in sheet name data which is in table form ( i need it to be in table form), in graph sheet i haave the graph of this data , im trying to attach a scrollbar to the graph so that at a time it displays a certain data n n lets me scroll thru but i keep getting #ref error in the offset formula in sheet data in cell F1...i just cant figure out where i m going wrong...i m attaching the sheet for ref...pls help...thank u
Named range for each category:
range.books =OFFSET(Table1[[#Headers],[books]],Sheet2!$H$1,0,Sheet2!$H$2,1)
Range.Date =OFFSET(Table1[[#Headers],[date]],Sheet2!$H$1,0,Sheet2!$H$2,1)
Range.Pencils =OFFSET(Table1[[#Headers],[pencils]],Sheet2!$H$1,0,Sheet2!$H$2,1)
Range.Pens =OFFSET(Table1[[#Headers],[pens]],Sheet2!$H$1,0,Sheet2!$H$2,1)
Graph data:
name:
="books"
x vals:
=dynatabgrah.xlsx!Range.Date
y vals:
=dynatabgrah.xlsx!range.books
- I moved the start date selector to shee2, H1 via scrollbar.
- books on secondary (scientific) axis
- added variable row/day count in shee2, H2 via scrollbar.
Last edited by protonLeah; 06-15-2023 at 11:13 PM.
Ben Van Johnson
wow...thanx...this works exactly as i wanted !!!
just wondering...is it possible to link each graph line to a button like books to a button pencil to a different button so i can select which one i want to display .
On sheet2(2) added three radiobuttons linked to A1
Choose dynamic chart title in A2 based oh A1:
New dynamic range (ordinate) for series1 based on A1:
Please Login or Register to view this content.
date range range.date2:Please Login or Register to view this content.
Please Login or Register to view this content.
my bad...wat i meant was if i wanted to turn off on multiple data on chart to do comparative study...by using radio buttons only 1 series gets turned on while i m hoping to get each one linked to separated button so which clicked either turn on or off the particular range from displaying....like say i want to do comparison of all 3 at a time or turn of maybe only book another and study pen vs pencils or turn of pencil n study pens vs books
I replaced the radiobuttons with activeX checkboxes linked to A1:A3.
I modified the named ranges for the categories to reflect the checkbox true/false condition: if true it returns the desired range, else zero. E.G., book2:
The chart title is assembled in D1 based on A1:A3,Please Login or Register to view this content.
Please Login or Register to view this content.
wow thank u so so much!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks