• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Ranges for Dynamic Dashboards

M

mantooth29

New Member
I've been working on creating my own dynamic dashboards using the 4 part series on this site, and it has been going very well (Thank you!).


However, I have decided I want the ranges on my data to be dynamic as well, which this series does not cover. I have utilized several resources to help create these dynamic ranges, but I am having trouble linking these ranges to the dynamic tables which are below our dashboards (as in the 4 part tutorial).


I know that my ranges are set up correctly (using offset function, and verifying the range location with f5). But in the tables, its returning #Ref! errors.


On Kyd's site, he talks about using the dynamic ranges to feed back into a database, which links to the dashboards. ( http://www.exceluser.com/dash/digest/setup/pnp-02-db-to-dashboard.htm )


So is it possible to use the dynamic ranges when building dashboards/tables as shown in the 4 part series, or do you have to link them back to a database?


If it is possible, any suggestions about what may be going wrong?


Thanks!
 
If I understand, yes it is possible. With the formula that you believe is correct but returns a #Ref! error, find out why it's getting an error with the "Evaluate Formula" button in the "Formula" tab of the Excel ribbon.


You can watch step by step to see what the formula does to get the error.
 
Thanks for the tip! The evaluate button helped me identify some trouble spots...


But it appears that keeping the value in INDIRECT() a dynamic range is causing the issue. If I change this one range to static, everything works.


All the other ranges in the SUMIF function are dynamic. But when keeping the KPI value dynamic, it still returns a #Ref error.
 
It's difficult to help if we can't see what you're trying to do. If you'd copy and paste the function you're trying to use onto the forum, it would make the comments a bit more precise.
 
Sure thing!


=SUMIFS(INDIRECT($C$20),Data!aSalesperson,B24,Data!aProductName,$C$18,Data!aMonth,$C$19)


This formula is in the dynamic table, which feeds the dashboard. The table is modeled after CH1 tab in Dynamic Dashboard Illustration Spreadsheet from the walkthrough.


The Indirect value is a named range KPI, that can be switched from total sales, to comissions, etc. The other conditions (eg, Data!Salesperson,B24) work fine even though they are dynamic. I'm guessing because I can define the range, and then define the value I am looking for.


The problem is with the indirect formuala. I have checked the range in Cell C20 with f5, and its parameters are correct. cAmount IS a dynamic range from the data tab. But this part of the formula returns the #REF! error. If I change this one range to static, then everything works as it should.
 
ok. What happens if you just put "=indirect($C$20)" in a cell on the same worksheet as the SumIf?
 
That still just returns the #Ref error. I am using a data validation for the list from input tabs, to refer to a dynamic range in the data tab. If that helps clarify.


I am thinking this has something to do with the way Kyd sets up dynamic ranges. With a name range on the column header (Sales, for instance) and then an offset formula for the dynamic range (aSales, for instance) that refers the Sales range.
 
The dynamic range set up you're describing works. Indirect just doesn't accept it as an argument.


You could try something like this instead of the indirect (I found it on Mr Excel):


=CHOOSE(MATCH(E2,List,0),Opt1,Opt2)


List = Names of the dynamic ranges

Opt1 = First range

Opt2 = Second range, etc.
 
You must log in or register to reply here.
Back
Top