Dynamic Named Ranges, Index, blank rows and Charts

[aiki100]

Greetings all -


I have been looking (here and elsewhere) to learn how to build dynamic ranges, AND for that data to be usable in a dynamically updating chart.


The desired end result: Dynamically updating chart, from dynamically updating range.
What please, is the best way to proceed?


Column B has data, but not every row actually has data in it (some are blank). I assume (and am checking here with you on this) that for dynamic ranges to work, that the data in the column must be contiguous, i.e., no blank rows, otherwise INDEX won't know where the last row really is.


Or, is there some way in which to accommodate this? If I had 10 rows of data, then two blank rows, then 5 more with data, I'd like the dynamic range to expand to cover the entire column, to the last actually entered data. Is this possible? I have many columns, and want to be able to generate charts off of each...


TWO: I have read that in dynamic ranges, one can use INDEX and OFFSET. INDEX being the less costly of the two.


THREE: I have also read that creating a dynamic chart can be accomplished via TABLES or FORMULA.


So, I'm getting a lot of variable information that I don't quite understand well enough to know what the best practice should be to accomplish the above.


Any and all help is very much appreciated!
Thx -

 

[arthurbr]

The last non blank cell in a range can be found with one of the techniques described at http://www.xldynamic.com/source/xld.LastValue.html
Also see https://contexturesblog.com/archives/2011/02/25/excel-function-friday-index-for-dynamic-range/
Using one of the formulas with the INDEX function you can build a dynamic range like =A1:INDEX..... (depending on the content of the range)
OFFSET is a volatile function which can cause slowdowns on larger sheets
As for the Tables see https://www.contextures.com/xlPivot01.html

As you can see, there is a lot of information out there. One does just need to search for it

 

[Aladin Akyurek]

1. Insert | Table is a good option.

2. Alternatively, we can create a dynamic named range. Need to know: What is the current range? What kind of data does the range house - text or numbers?

 

[aiki100]

Thank you both for your responses - it is much appreciated.
@arthurbr - The links look good, Thank you very much for posting them -- I have been to some of those already - there is indeed a TON of info, but not much in the way of actual best practices (there being many easy to proceed, it appears) -- thus I am grateful for your real world practitioner experience.

@Aladin Akyurek - Thanks for your thoughts - yes, this is all about named ranges and the (best way) to use them to generate a dynamic chart. My data is numbers, tho there could be some text, in other uses, later on -

The easiest way seems to be to create a table (or in my case, convert already existing data into a table) and any chart created from that table will be dynamic. But there is a lot of info on building named ranges using INDEX and OFFSET, and I (assume) there must be some good reason these options exist, in terms of volatility, speed, large data sets, etc. or special properties I have no idea about yet (and thus asking here...)

If you (all) tell me to just use the table version and be done with it - I will go with that, and be very grateful for the insight...

Wishing you all a very good New Years -
Thx

 

[Aladin Akyurek]

If the table option appears to slow your spreadsheet, creating a dynamic named range is the best option. If the latter is needed, post the current range.

 

[aiki100]

Hi Aladin - thanks for the response.

So far, this sheet is only a few hundred rows, but I have others I will have to work with that are possibly several hundred thousand - so tables are bad for large data sets?

Also - since I posted this, I did move on to working with tables and hit a snag I just posted about that is related to all of this above.

I don't know if this will work as a link, but here is the name if the post, if you think you might have some thoughts on it.

[h=1]Filtered tables and running totals[/h]https://www.mrexcel.com/forum/excel-questions/1082819-filtered-tables-running-totals.html

All very much appreciated, I hope you know!
thx

 

[Aladin Akyurek]

Since you want to create charts, the Excel table looks convenient (to me) for working with the filtered data.