Dynamic Named Ranges, Index, blank rows and Charts

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
89
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 -
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,311
Office Version
  1. 2010
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Aug 16, 2016
Messages
89
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
Aug 16, 2016
Messages
89
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Since you want to create charts, the Excel table looks convenient (to me) for working with the filtered data.
 

Forum statistics

Threads
1,144,162
Messages
5,722,847
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top