Help with Named Range to use in Chart

[Jacobs22]

Hi,

I am trying to create a report with graphs in excel, where i can paste a data set in one worksheet, the and the charts will update on a different sheet in the same workbook. My data set will change in length, so i set up a formula to adjust for that using offset and countif.

however, my data can also start on a different row sometimes, so i need a way to change the 1st cell of the range. my data is generally laid out like this

I was able to create a named variable by using =MATCH(1,$A$1:$A$10000,0), which returns 54, which will be the 1st row of my data. But i am having trouble incorporating this named variable into my Offest/Count formula.

any suggestion on how i would write/format this? basically i am trying to create a range from C54 to C last row. but the first row of data (C54) may change. the first row of data will always have a 1 in column A.

 

[Jeffrey Mahoney]

This formula can be applied to each individual column of your data or the whole table if you change one parameter. Just change the column reference. One caveat, if any of your columns will not have data in every cell, specifically the last cell, you'll need to alter it a bit. Choose the column that WILL have data in every cell and use that as your "Counter" column. Every column reference to B in the formula directly below after the first one would be changed like in the second example. If you want it to include the entire table, then change the last parameter "1" to 5 or 10.

=OFFSET($B$3,1,0,MAX(($B:$B<>"")*(ROW($B:$B))-ROW($B$3),1))

=OFFSET($B$3,1,0,MAX(($A:$A<>"")*(ROW($A:$A))-ROW($A$3),1))