Error when trying to Create Dynamic Chart with named ranges
I have made charts with named ranges before, I can't figure out why I keep getting the incorrect reference error when I try to add my named ranges to the data series.
In the attached spreadsheet I have tried creating a chart with the series as
='Dynamic_Chart.xlsx'!A
to plot the A series but it doesn't work and I can't figure out why. Any help would be much appreciated!
Thanks,
Kelsey
Unless "'Dynamic_Chart,xlsx" is a sheet name, your syntax is in error. Workbooks are enclosed in brackets.
might be a better expression.
Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Possibly the INDIRECT it doesn't like. Maybe try it like this:Formula:
Please Login or Register to view this content.
Last edited by TMS; 03-07-2019 at 04:36 PM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks JLGWhiz for the reply! I tried your solution with brackets with no luck. I also tried without adding the sheet name since these named ranges are available to the entire workbook and it also didn't work. Is it working on your end with this solution?
Mmmm ... have you tried my suggestion?
I didn't try that but I did test the range for the series and it appeared to be OK. Maybe @TMS is onto something.Originally Posted by kelseygueldalewis
I am playing around with it now, your solution had a fixed beginning of the range but mine needs to be dynamic, going back 12 entries as the weeks go by but I think this is on the right track. I will post if I solve by removing the indirect.
I'm stuck on trying to get the origin cell reference used in the OFFSET formula of my named range without using indirect. I am using the current date as the last value in the named range, and in A15 calculating what column number corresponds to this date. I'm not sure how to do this without indirect.
This:Formula:Please Login or Register to view this content.
Or, if you want to miss out the "middle man" ...Formula:Please Login or Register to view this content.
Yes this worked! Thank you! I will update my formulas accordinglyOr, if you want to miss out the "middle man" ...Formula:Please Login or Register to view this content.
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks