Create Numerous Dynamic Range

[Phoenix5794]

I'd like to take the tedium out of creating these dynamic ranges. Currently I have upwards of 30 'lists' which I've used this formula to give it an offset value to remove any blank spaces that might show up in my list:

Please Login or Register  to view this content.

This is fine, but my issue rests in the redundancy of pasting this code into every defined name and then changing the 3 variables denoted by a letter (whether it be for column A, B, or C).

Does anyone have any ideas on how I can automate this process? I'm unfamiliar with all of the abilities of the name manager in Excel 2010.
Perhaps there is even a way to generate these lists for Data Validation without using this code (a built-in Excel function).

Thanks a lot!

[Phoenix5794]

I've created an example here: ListExample.xlsx.

So let's say I have several lists of items (that span all the way from column A to Column AF or more), it's quite a tedious task for me to go in and define each range individually and apply this code

Please Login or Register  to view this content.

and change the 3 column variables for each list to be created. It's simple enough for one or two, but try it yourself - it's time consuming.

My crazy idea in mind was that excel had some feature to sort of 'drag and copy' the naming rules, not unlike the ability to grab the bottom right corner of a cell and 'smart copy' it down a column or row.

Any help would be greatly appreciated, thanks!

[FDibbins]

im unsure what exactly you are tring to achieve here, but if you want to count values in a column, and the column number changes, have you considered using index/match() to find what you need?

alternatively, maybe you could give a better explainaition of what you want?

[Phoenix5794]

alternatively, maybe you could give a better explainaition of what you want?

Heh, sorry. I seem to have a difficult time conveying my question to others.
In essence, what I'm looking for is the process of me going through and manually creating the lists. Currently I would have all of my cells in place:

• A header for every column with line items below them which could go on for 30 or more columns (so columns A through AD would have a list).
• I would then define each range.
• To do this I would select my header, Define Name, and for the refers to section I would put in my formula (=OFFSET(Lists!$Y$2,0,0,COUNTA(Lists!$Y:$Y))).
• From here is where the more tedious step takes place - I would then change all the Y's in that formula to whatever column is needed.
• By the way, this formula is used to create a dynamic list because the list may or may not change and because other users will be using this form, I'd like them to simply be able to input another item next in the list. This will then count to the last value and avoid the case where blank spaces will fill my drop-down list.

I hope this explains why I'm doing it this way and what my intentions are (create a more automated process to generate these lists with the semi-unique formula).

If it's still unclear as to what I'm trying to do, I suppose I could make a video or something. lol

[royUK]

It seems strange that you are bothering when you have Excel 2010 & the Table feature equates to creating Dynamic Named Ranges

[Phoenix5794]

I'll check it out, thanks Roy.

[Phoenix5794]

Ok, I created a table with all of my headers and list items.
How do I incorporate Data Validation with this to create a drop box list on a separate page? I'm thinking something like setting the source to

Please Login or Register  to view this content.

or something similar (making up syntax).

And more importantly, let's say I've created this table with 30 blank spaces beneath (vertically) the last list item, would I then be able to not see those blank spaces at the end of my drop-down box (created from my Data Validation)?

Thanks.

[Phoenix5794]

Here's the updated example:
ListExample2.xlsx

[SkilledLantern]

why do you need the red boxes?

[Phoenix5794]

The red boxes are just to indicate the blank spaces that I'll leave available for other users to input (if by chance they have a new list item they'd like to add).

[royUK]

Ok, I created a table with all of my headers and list items.
How do I incorporate Data Validation with this to create a drop box list on a separate page? I'm thinking something like setting the source to

Please Login or Register  to view this content.

or something similar (making up syntax).

And more importantly, let's say I've created this table with 30 blank spaces beneath (vertically) the last list item, would I then be able to not see those blank spaces at the end of my drop-down box (created from my Data Validation)?

Thanks.

See this it's a instructions on how to use a Table as a dynamic source for Data Validation

[Phoenix5794]

See this it's a instructions on how to use a Table as a dynamic source for Data Validation

Thanks for the tutorial Roy.

This still presents an issue regarding my dropdown lists. I have my table setup with all of my lists, but my data validation dropdown lists still have blank spaces at the end. Also, when I add an item to a table's list, a new row will be generated, but if I delete that last item, that formatted row stays in place.

Not to mention, when I define the name of each range it refers to a source with the syntax of

Please Login or Register  to view this content.

(<> brackets not included) which means I have to go in and delete the [#Headers] part every time along with the comma and outside brackets. Did you run into this issue as well, or is this something new? You can see what I mean by defining List7's range.

Here's my example:
ListExample3.xlsx

Thanks.

[Phoenix5794]

Is there someway when referencing my table's range that the blank spaces do not show up in my drop-down box?
I have =Table1[ListName] as my defined range and when I data validate that range (ListName), the list includes all of the blank spaces that are in the table.