Excel - ref to last row used

[Lily the Pink]

I have a spreadsheet on which I add a new row each day. Column A contains the day's date, columns B-M contain data for that day. I display all this data on a simple multi-line graph. From time to time I adjust the data ranges for the graph to extend beyond the current day. I could of course just specify the range for each column to go up to eg 10000, but that seems inelegant and I would prefer to specify the range as for example "B3:B[lastrow(A)]". That is of course just an invented notation. Is there any way of achieving this without resorting to VB ?

[Ultuous]

You can use dynamic ranges (good explanation here: https://www.excel-easy.com/examples/dynamic-named-... ) but usually I find it quicker, easier and neater these days just to reference the whole column(s) (e.g. 'B:B' or 'B:M') rather than specify a selection of cells within them!

[CubanPete]

You could set up a separate table for the graph and fill it using =vlookup or =max or dates ranges using drop downs.

[Mr Pointy]

Maybe format your data as a table - see around 8 minutes for how to add data to the bottom of a table:

https://www.youtube.com/watch?v=Du73CPqWGQw

[Doofus]

If column A contains the date, then the last row used will be the largest number.

A1:INDIRECT("A"&MATCH(MAX(A:A),A:A))

[paulrockliffe]

Absolutely the way to do this is format your data as a Table, then do all your data visualisation using Pivot Tables and Pivot Charts that reference this names Table.

Answering your question more directly, the simplest way to do it without doing it properly is to extend the range and then add a filter so blank rows are excluded. I think anyway, honestly do it with a names able and a Pivot Chart.

[MintyUFB]

I use =offset(sheet1!$a1,0,0,counta(sheet1!$a:$a),counta(sheet1!$1:$1)) as a named range