How to autofill multiple drop-down lists?

filido

New Member
Joined
Jun 7, 2019
Messages
21
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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/
 
Upvote 0
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top