I have a row which is filled in with data for each week.
A graph is made out of this data.
now i would like to have a dynamic named range which will add the next week when the cell is filled with data.
This dynamic range should then be linked to the graph so the graph will automatically update with the new week.
see test workbook.
there are 2 names in WB _q for data and _n for week no
Slightly different approach. Using 3 named ranges.
1 for series data, 1 for x_axis and another for series label.
Recreate those named ranges, then edit series formula by highlighting series.
Making sure to add worksheet name in front of named ranges.
=SERIES(test.xlsx!Series_Label,test.xlsx!x_axis,test.xlsx!Series,1)
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thank you both.
Making the named ranges I understand now, however it seems I cant get those linked to my graphs.
For example I named my range for the weeknumbers 'week' then I fill in the axis label range =name of the file.xlsx!name of the range
The same as you do in the test files but for me it does not work..
Upload an example where it is not working (Go Advanced > Manage Attachments) so that we can troubleshoot
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
you should input named ranges in graph data source this way: sheetname!definedname
Did it like that
Please Login or Register to view this content.
Oh, in my post. I meant. Workbook name, not worksheet name.
=SERIES(test.xlsx!Series_Label,test.xlsx!x_axis,test.xlsx!Series,1)
Replace sheet reference with workbook reference and named ranges should work.
The easiest way (for me) to insert named ranges into graphs is, with the graph selected, choose Design tab on ribbon and then "Select Data"
Choose each series and then Edit
=sheetname!definedname ie. = 'Inbound vs outbound week'!Inboundzig
repeat for all series.
Some notes:
It might be due to you editing but the Inboundzig name was not set up right. You referenced the COUNTA to row 2 instead of row 4 (where the data actually is)
I assumed that the number of data points in Inbound Z will be the same as all the others so I used this shortcut in creating the other defined names
outboundzig = Offset(Inboundzig,1,0)
InboundU = Offset(Inboundzig,2,0)
OutboundU = Offset(Inboundzig,3,0)
and so on.
Any questions?
Last edited by ChemistB; 07-05-2017 at 02:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks