Excel using a string as a cell reference

Multi tool use
Multi tool use


Excel using a string as a cell reference



I am trying to create a dynamic graph that checks which financial year it is and then only graph the data for that range of months.



I have a 'SUMMARY DATA' sheet that has rows with the date range 1/7/18 || 31/7/18 and so on for every month. I am trying to create a dynamic range that takes these values and then have the graph use those cell references.



Currently I am using a fancy concatenate function that gives me 'SUMMARY DATA'!$H$25:$H$36 (In cell DASHBOARD!$AJ$24) which is the range that I want to reference. I can't seem to get this working with a graph though. If I place =DASHBOARD!$AJ$24 as the series values, the graph doesn't update with the correct values (just 0 for every column). If I try using =INDIRECT(DASHBOARD!$AJ$24), it gives me a "That function isn't valid" error.


'SUMMARY DATA'!$H$25:$H$36


DASHBOARD!$AJ$24


=DASHBOARD!$AJ$24


=INDIRECT(DASHBOARD!$AJ$24)



Not sure how to progress




1 Answer
1



First, create a named range (Formulas, Defined Names, Name Manager) called cdata with a Refers to: of,


=indirect(DASHBOARD!$AJ$24)



Now use =Dashboard!cdata as the chart data source.


=Dashboard!cdata



Be advised that these series data references are resolved; they do not stay dynamic. i.e. changing the reference in DASHBOARD!$AJ$24 does not automatically alter the chart data.





Perfect. It works, but is there a way to make it dynamic? Whilst the value would only change every 6-12 months, it would still be nice to have it update when needed
– Maxim Srour
Jul 2 at 0:55





I typically use vba to reassign a range to the data series. Record yourself performing the action and then edit the code to suit your purposes.
– Jeeped
Jul 2 at 0:57






A worksheet_change event sub based on changes in DASHBOARD!$AJ$24 should do it.
– Jeeped
Jul 2 at 0:59





Wouldn't it be editing the named range so that it recalculates? Isn't that how I would get it to reset?
– Maxim Srour
Jul 2 at 1:13





No, a worksheet_change event sub based on changes in DASHBOARD!$AJ$24 will eliminate the need for the interim named range.
– Jeeped
Jul 2 at 1:18






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

FvWWJUYWxgEMQQMs GT,gw,cR,dGjeLJrIdbuH83EexyYu C7,nin 5ZHNntc1,CReG 0a
WBG OSmiJbQv eC2FmQuVGrz6v4S mQupQXZ9mhbf

Popular posts from this blog

Rothschild family

Boo (programming language)