Forum Discussion
Dynamic range can't be used in a graph
Hi,
I have used the same dynamic maned range for 11 of these graphs, and only one is giving me problem.
This is the formula
=OFFSET('Progress'!$B$6,0,COUNTIF('Progress'!$C$9:$AS$9,">=0")-chtlen,1,'Progress'!$AI$1)
The formula basically looks along to the blue highlighted row below and counts along until it hits a blank cell then stops and the charts updates automatically whenever a new % is added to this blue row. The same formula is used for the date along the top too however obviously references the orange cells not the blue.
However, whenever I try and change the chart to use this progress named range (blue) instead of selecting the cells manually for the series, it doesn't seem to recognise the cells like the date one does.
Date dynamic chart: you can see the dates along the top highlighted in a green box when I open name manager and select the date dynamic formula.
Progress dynamic range: you can see the progress isn't highlighted in a green box when I open name manager and select the progress dynamic formula.
I also get the following messages when I try to change the current manual progress data in the series to the dynamic progress one:
Does anyone have any idea it's not recognising or identifying the data?
3 Replies
What is chtlen?
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
fairly simple fix in the end, I'd referenced a different row and not realised till I read both your comments!
- I don't understand. It sounds like you are trying to create a dynamic range for a graph but the formula doesn't match that:
=OFFSET('Progress'!$B$6,0,COUNTIF('Progress'!$C$9:$AS$9,">=0")-chtlen,1,'Progress'!$AI$1)
so the format is OFFSET( [starting range], [offset rows], [offset cols], [# rows], [# cols] )
so you start at B6 and offset 0 rows (good) and offset columns an amount base on some count (not sure you want that), then define it to be 1 row tall (good) and exactly some # of columns based on the value in AI1 (doesn't make sense)
I don't know what is in AI1 and why it is here and I don't know why you subtract some variable called "chtlen"
I suspect you want that 'count' in the 4th parameter and I don't know if the 2nd parameter should be 0 or that AI1 value. but this is a bit of guess work
