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")-cht...
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
=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