How to autofill multiple drop-down lists?

[filido]

Hi all,

I have a long list (several hundred rows) and I am trying to create drop-down lists that each contain about 5-6 options of my list. I'm sure this can be done with VBA but is this possible to do with basic Excel functions? For instance,

1. drop-down list contains cells A2:A5,
2. drop-down list contains cells A6:A10,
3. drop-down list contains cells A11:A14,

etc.
Rather than do this one list at a time, can I somehow automate this?
When selecting range for the drop-down list, I've tried $A2:$A5 (instead of default $A$2:$A$5). This doesn't make a difference.

 

[DanteAmor]

Hi all,

I have a long list (several hundred rows) and I am trying to create drop-down lists that each contain about 5-6 options of my list. I'm sure this can be done with VBA but is this possible to do with basic Excel functions? For instance,

1. drop-down list contains cells A2:A5,
2. drop-down list contains cells A6:A10,
3. drop-down list contains cells A11:A14,

etc.
Rather than do this one list at a time, can I somehow automate this?
When selecting range for the drop-down list, I've tried $A2:$A5 (instead of default $A$2:$A$5). This doesn't make a difference.

There is a pattern in the cells to know which cell to which cell goes in a drop-down list.

You can also create a name range for each of the cell ranges.
https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

 

[filido]

There is a pattern in the cells to know which cell to which cell goes in a drop-down list.

You can also create a name range for each of the cell ranges.
https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

How can I include this pattern to drop-down lists? Creating name ranges is not efficient because I'd need to create 600/5=120 different ranges.

 

[Fazza]

If you set it up a bit differently the names can be created, though VBA offers benefits.

Such as if the first name is in A1 and the cells are B1:F1
then A2 name for cells B2:F2
then A3 name for cells B3:F3

select the whole lot and then CTRL-SHIFT-F3
and create names from values in the left column

 

[DanteAmor]

How can I include this pattern to drop-down lists? Creating name ranges is not efficient because I'd need to create 600/5=120 different ranges.

We need you to tell us if there is a pattern in the cells to identify from which cell to which cell a dropdown should be created.

 

[Fazza]

VBA opens a new universe of possibilities. Are there reasons to not use VBA?

 

[filido]

VBA opens a new universe of possibilities. Are there reasons to not use VBA?

I think I have to rely on VBA now. I was reluctant to use VBA at first because i don't really know how to code. Do you have any sugggestions how to write this?

And to that pattern question: There isn't actually a solid pattern since some ranges may include 4 cells, some 5 and some 1 etc. It varies a lot.

 

[DanteAmor]

I think I have to rely on VBA now. I was reluctant to use VBA at first because i don't really know how to code. Do you have any sugggestions how to write this?

And to that pattern question: There isn't actually a solid pattern since some ranges may include 4 cells, some 5 and some 1 etc. It varies a lot.

Without a pattern, it could not be automated.
You will have to manually perform each validation list, you can support yourself by naming ranges.

Another idea would be to put each list in a column,

1. drop-down list contains cells A2:A5,
2. drop-down list contains cells B2:B6,
3. drop-down list contains cells C2:C2,

That way it would be easier for you to create each range name.



And to all this, where would you put the validation lists?

 

[Fazza]

structure the data logically, then someone can code it.
I'm thinking a dedicated worksheet - it can be hidden by code - set out with a table or contiguous blocks of data to define the names for the ranges and the cells in each range.
it might not matter too much how it is structured/setup so long as it is consistent: that way code can be written to work with it

before starting though, it might be best to describe in words what is wanted. maybe how it will be used too (Not much point in having code quickly make 120 named ranges and then to use them in the spreadsheet takes hours to manually set up)