Dynamic Ranges and Dynamic charts - Chart has problems with formula references

[dazedniteman]

Hello!

I'm trying to create a dashboard for work that will update based on user selections. I'm trying to create a dynamic chart and am having issues and hope someone can help. For reference, I am trying to follow the steps outlined here: https://www.youtube.com/watch?v=ShoX3krTqvs

Basic layout of the sheet:
Input sheet -> user enters data once a month

Data dump sheet -> all the data is dumped into this sheet behind the scenes and collects previous data and most recent data

Reformated data dump -> Uses an Index/Match function to sort and bring over the data from the data dump. This is organized by month and year and also the various metrics. This sheet works correctly. To note: The dates across the top go back a few months and may not have values, resulting in a #N/A (that's fine for now, I'll use IfError to hide these later). the values for the months with data show up fine.

Graph Prep -> The idea will be to allow the user to select which location to look at. So this table has the months across the top and metric names down the left column and the cells have this formula: =IFERROR(VLOOKUP($D10,INDIRECT($F$4,TRUE),Q$6,FALSE),"")
This formula works. I'm using Indirect because F4 is the user selection and that corresponds to a named range. So the table here, again, has months from Jan 2014 through Dec 2015 across the top. There is no data before Jan 2015 right now so those cells are blank because of the formula up above. Starting in Jan 2015 through July 1 2015 (I'm testing the formula out so I put a few months of blank data in there) there are values that are being pulled over thanks to the formula. After July there are more blank cells through the end of the year.

I want to have graphs that show the trend in values over time (and soon I want the user to be able to define a set timeframe to evaluate (i.e. the user will be able to select an option to look at the previous 3 months of data and also select the time period to look at). For now, I am trying to get the big trend working.

Because there are blank cells before the cells with values, I have a column to identify the first non-blank cell address =ADDRESS(ROW(D10),COLUMN(INDEX(E10:AB10,MATCH(TRUE,INDEX((E10:AB10<>""),0),0))))
This is correctly pulling the cell address.
I also have a column that should be getting the range of all the cells with values. This formula is: =INDIRECT($C$10):INDEX(INDIRECT($C$10):$AB$10,COUNT(INDIRECT($C$10):$AB$10))
C10 is where the address formula above spits out the first non-blank cell address. When I highlight the formula and press F9 it gives all the values I am looking for.

I go to Name Manager and create a new named range called TestStaff and Refers To has the equation above. When I click the button next to the text input it correctly highlights the cells with values.

I create a chart and go to Select Data, under Legend Entries (Series) I click Edit and keep the worksheet name ='Graph prep'! and press F3 to paste the name of the named range TestStaff. To the right it shows the values " =5, 5.53... "
I click OK and it gives me an error "We found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names,, and links to other workbooks in your formulas are all correct."

Can someone help??

 

[dazedniteman]

Well, I think I figured it out, but I'm not sure why exactly. Doing a lot of Googling, It seems like people have had issues with using Indirect functions in dynamic ranges. I created a new formula that is currently working:

=INDEX($E$10:$AB$10,MATCH(TRUE,INDEX(($E$10:$AB$10<>""),0),0)):INDEX(INDEX($E$10:$AB$10,MATCH(TRUE,INDEX(($E$10:$AB$10<>""),0),0)):$AB$10,COUNT(INDEX($E$10:$AB$10,MATCH(TRUE,INDEX(($E$10:$AB$10<>""),0),0)):$AB$10))

I'm not sure if it is the easiest or cleanest formula, but it is working right now and is ignoring the blanks before the first number and is updating. I'd be happy if anyone could explain why the other formula doesn't work or if there is a better way to write this formula.

Thanks so much.

 

[dazedniteman]

For some reason, Let's say I have data in January, February, June and July but March, April, and May are blank, the count function for the graph isn't functioning perfectly. Is there a way to have the formula ignore the blank cells?

Right now the graph shows a zero (which is ok) but the count function is still working and it cuts off the graph before the end. So with the example I gave, Jan-July is 7 months, but there are only 4 months with data. The graph would show values for Jan, Feb, March and April and end there. It says hey, there should be four values so let's count to four.. it does so but still counts the zero values in the graph so it ends early.

Thanks for helping me through this