1. 

   mantooth29
   

   Member
   Joined: Oct '11
   Posts: 12

   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!

   Posted 1 year ago #
2. 

   jeremymjp
   

   Member
   Joined: Oct '11
   Posts: 71

   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.

   Posted 1 year ago #
3. 

   mantooth29
   

   Member
   Joined: Oct '11
   Posts: 12

   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.

   Posted 1 year ago #
4. 

   Mike86
   

   Member
   Joined: Sep '11
   Posts: 87

   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.

   Posted 1 year ago #
5. 

   mantooth29
   

   Member
   Joined: Oct '11
   Posts: 12

   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.

   Posted 1 year ago #
6. 

   Mike86
   

   Member
   Joined: Sep '11
   Posts: 87

   ok. What happens if you just put "=indirect($C$20)" in a cell on the same worksheet as the SumIf?

   Posted 1 year ago #
7. 

   mantooth29
   

   Member
   Joined: Oct '11
   Posts: 12

   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.

   Posted 1 year ago #
8. 

   Mike86
   

   Member
   Joined: Sep '11
   Posts: 87

   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.

   Posted 1 year ago #
9. 

   mantooth29
   

   Member
   Joined: Oct '11
   Posts: 12

   You sir, are a genius, and have solved my problem. Thanks so much!

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics » Excel for Beginners - Tutorials » Excel Formula Examples » Array Formulas » Excel Charts » Pivot Tables » Project Management using Excel » Excel Dashboards » Excel VBA » Advanced Excel

Our Products Full Training Programs » Excel School » Excel Dashboard Classes » Excel VBA Classes » Excel Financial Modeling » Excel for PMs Crash Courses » Excel Formula Course » Excel Dashboards Course Downloads » Excel PM Templates » Excel Formula E-book

About us » About Chandoo.org » Welcome » Best of Chandoo.org » FREE Excel Downloads » Contact Details » Excel Forums