Create a Named Range in Excel That Grows Automatically

[@christophernissen1591]

13 years later and this advice is still perfect. Exactly what I needed. Thank you

[@contextures]

You're welcome, Christopher, and thanks for letting me know that the video had what you needed. I appreciate it!

[@carlospitcher4335]

This video is almos 12 years and it has a great quality, great explaning and goes to the poinyt. Wish YT pushed more of this content!

[@contextures]

Thanks, Carlos, and it's great to hear that my video helped you, after so many years!

[@kimdbse]

I used to use this quite often a long time ago in the earlier version of Excel. I knew what I needed to do but didn't quite understand everything in the formula. Now I know exactly what the formula is doing. Great instructions!

[@mikep.7146]

Hi, type A and B in two cells in the same column. Convert those two cells to a matrix from "insert" tab. Now you define a name on those two cells and every time you add something the matrix expands and so does the named range. So if you add C in the cell directly below B, the matrix expands and the named range contains 3 values now. Very useful for creating lists.

[@michaelm839]

Thanks, well explained, with a pleasant pace and voice

[@ivorynd]

Thank you. I learned something new.

[@SonarMan645]

Thank you! Helped me remember some old tricks of the trade. Great video.

[@ToppikEU]

I thing this should be easily achieved simply by creating the Table via the Insert menu. Name it then. Once a new row within this table is created the name range is automatically updated. Works for me..

[@magicstarqtpie09]

Awesome! Thank you so much - extremely helpful!

[@MrCJsssJr]

Wow been struggling with this so much. Thank you for pointing out that it won't show, but if you click on "Refers to" then it does! I thought I kept doing something wrong. Thank you!

[@יוסיבוקר-ד6ו]

Clear and well pointed video. Thank you!

[@markorafferty876]

What happens if there are more rows used in the column that you don't want to include? These would be in another table below the existing table. Using COUNTA in that situation could cause circular references.

[@contextures]

If possible, format the lists as named Excel tables. After that, you can create other names, based on the table columns. My video at this link shows how to create a named table: kzbin.info/www/bejne/d36amoh5jriIqNk

[@malcolmt3125]

This is very useful but it does not work when I select a range that is input from another workbook. To clarify, the table it is using refers to a remote workbook, the data is dragged into a separate worksheet and that is the table content.I have left capacity for the table to grow as the data in the remote workbook grows (Dynamic). Removing the empty rows make it work but does not allow for expansion of the table without manually expanding the range.Can this be made to work?

[@neversayjello]

why doesn't a dynamic named range show up in the Name Box above A1?

[@contextures]

I'm not sure why the dynamic named ranges aren't listed there, but that's the way it's always been.

[@bobwebber8521]

I concur with Micheal M. That was made painless.

[@trisaliles3254]

I'm using this Dynamic Named Range with this formula - SUMPRODUCT(SUMIF(INDIRECT("'"&MonthsDynamic&"'!d9:d101"),$D8,INDIRECT("'"&MonthsDynamic&"'!f9:f101"))) -- in several different cells, but only 1 cell returns a value. Help!!!!

[@jacoh9591]

Fantastic - Definitely made my life easier. Thanks

[@vjethk]

Is there a way to add new columns to this Dynamic range?

[@contextures]

You could change the last number in the formula from 2 to 3 (or any number of columns). Or, use COUNTA in the last argument too, to counta the entries in row 1 : =OFFSET($B$1,1,0,COUNTA($B:$B)-1,COUNTA($1:$1))

[@howlian]

is there a possibility to expand an array where a formula is applied within? E.g. an array which needs to calculate something automatically for every update to another array

[@Heliumcool1]

So does excel calculate the dynamic range when you click the drop down box, or does it recalculate the range every time the workbook is recalculated? In other words does it reduce the performance?

[@jacoh9591]

I doubt I'd get a reply on this but once doing this, How would you make 1 list out of multiple lists constructed this way?

[@rock78785]

Thanks, It helped me a lot.

[@NYCGIBB]

Perfect, this is exactly what I was looking for. It DOES work in excel 2010.

[@shitalakshyacommunication9562]

Thank you for this best VDO

[@Youtubervideos125]

May i ask when am doing the offset why it the refer naming the sheet name and not the range name i have created?

[@Romariowll]

I LOVE YOU - FROM BRAZIL

[@freshpradeep]

Thanks.Helped in my project work...

[@yusdur28]

nice content, thanks

[@contextures]

You're welcome, and thanks for your comment!

[@james02Perry]

Nicely explained. I use a Mac. I can't find the NameManager.....???

[@marks6663]

this software is so clunky. so unintuitive. I still don't understand how I get a subtotal to automatically update as I had another row. I followed what you did but it still does NOT add up my values.

[@kevinfischer5564]

Is there a formula I can use if I am trying to name multiple dynamic ranges that exist in the same column and all grow?

[@contextures]

It might be possible, but it depends on how the ranges are set up. Perhaps do a count, and subtract the count of cells above each range's starting cell?

[@SmexMuffinMan]

over 9000 Internets to you! Thanks alot!

[@Pumpkinjews]

Life saverrrrrrr thank u thanku

[@stefanocarocoforo8822]

Thanks so much this helped

[@andah10]

Works perfectly in Excel 2010

[@TheEngineeringToolboxChannel]

Or just use tables :)

[@coreygraham860]

For some reason, mine is including the next blank row under the last entry.

[@coreygraham860]

Never mind. I changed the first number to 0 and it works fine now.

[@rootsalad]

love it

[@matth4745]

Homerun Ma'am!

[@mubashir518]

very good

[@shooter7a]

Good god could type any slower?

[@ron37wb]

I'm not understanding it.

[@kgranger73]

You obviously had an error in typing your formula because it does work flawlessly in 2010 as well as 2013. So tell me, how's Libreoffice treating you? Rather than being a rude person you might have asked for help.

[@mosabbarhussain128]

Mosabbar hussain

[@mosabbarhussain128]

md

[@mosabbarhussain128]

Mosabbar hussain