How to use expandable list for autofilling cells?

M

Miray

New Member
Joined
Jan 7, 2004
Messages
4
hello all,

The problem i am facing can be best illustrated using the following example

I have a list of industries - communications and electronics, and for indu01 i have two manufacturers: siemens and nokia; for electronics i have dell and acer.

then I have this search worksheet, which will work like this: in cell "A1", there is a drop down list of industries. if i select "communications", then in cell "B1" manufacturer drop-down list, only "siemens" and "nokia" will be up for clicking.

Now comes the part that bothers me. all of the industry and manufactures have to be dynamic or expandable. For example, the industry will include chip-making industry, chemical and so on. So will the manufacturer lists -- the communication industry manufacturer list will be added Ericsson, Samsung...the electronics one will add Samsung, LG, Toshiba...

I did a search on threads and found some useful old threads, such as
http://216.92.17.166/board2/viewtopic.php?t=60461&highlight=data+validation+category

where, however, all lists are fixed in range.

I tried to use offset() function to make lists expandable, but then indirect() function would not work.

Any suggestions?

Rgds
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
You could stick with fixed range lists w/room for expansion, ie. Communications = C2:C10 even if there are only 2 entries, Electronics = D2:D10, etc. If more entries are needed, insert rows above row 10.

Your dropdown list for Communications would include your 2 entries plus 7 blanks. If you want to eliminate the blanks in the list, for Data Validation List Source in B1 put
=OFFSET(INDIRECT(A1),,,COUNTA(INDIRECT(A1)))

You can make Industries dynamic in this way also, ie. Industries = C1:H1. Data Validation List Source in A1 put
=OFFSET(Industries,,,,COUNTA(Industries))
 
Upvote 0
Thread to look at:

http://www.mrexcel.com/board2/viewtopic.php?p=154

where each list can be defined by means of a dynamic formula.

Suppose that all the relevant lists are on Sheet1 and INDUSTRIES are in column A starting at row 2.

Activate Insert|Name|Define.
Enter BigStr in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click Add.

Enter INDUSTRIES in the Names in Workbook box.
Enter the following in the Refers to box:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(BigStr,Sheet1!$A:$A))

Click Add.

Continue defining the other names and when ready, click OK>
 
Upvote 0
Aladin Akyurek said:
Thread to look at:

http://www.mrexcel.com/board2/viewtopic.php?p=154

where each list can be defined by means of a dynamic formula.

Suppose that all the relevant lists are on Sheet1 and INDUSTRIES are in column A starting at row 2.

Activate Insert|Name|Define.
Enter BigStr in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click Add.

Enter INDUSTRIES in the Names in Workbook box.
Enter the following in the Refers to box:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(BigStr,Sheet1!$A:$A))

Click Add.

Continue defining the other names and when ready, click OK>
Click to expand...

Slick MATCH function, I like that. Note that A:A must be empty below list for MATCH to work.
 
Upvote 0
Aladin Akyurek said:
Thread to look at:

http://www.mrexcel.com/board2/viewtopic.php?p=154

where each list can be defined by means of a dynamic formula.

Suppose that all the relevant lists are on Sheet1 and INDUSTRIES are in column A starting at row 2.

Activate Insert|Name|Define.
Enter BigStr in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click Add.

Enter INDUSTRIES in the Names in Workbook box.
Enter the following in the Refers to box:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(BigStr,Sheet1!$A:$A))

Click Add.

Continue defining the other names and when ready, click OK>
Click to expand...

The INDUSTRIES list can be dynamic using Aladin's INDEX/MATCH formula above, but the question from Miray is can the dependent list be sourced from a dynamic range. I don't see that it can, thus my fixed range proposal.
 
Upvote 0
Aladin Akyurek said:
Scott R said:
...

The INDUSTRIES list can be dynamic using Aladin's INDEX/MATCH formula above, but the question from Miray is can the dependent list be sourced from a dynamic range. I don't see that it can, thus my fixed range proposal.
Click to expand...

The answer is simply yes. Anywhere in the workbook you can data validate an appropriate cell with Allow set to List and Source to INDUSTRIES:

=INDUSTRIES


Why do you think it can't be done?
Click to expand...

Should have been more clear... The Industries list using =Industries can clearly be done. But once an industry has been chosen, the dependent list (Communications companies) cannot be sourced via =INDIRECT(ref) when ref is a dynamic range. That's what Miray is trying to accomplish since all ranges are dynamic.
 
Upvote 0
Thank all of you for kind of help

didnt expect response was so swift and I had to go back to page 5 to find this thread and to post this thank-you note

Thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
755
dmt32
D
K
  • Question
Autofill Cells with Drop-down List Until Last Row using VBA?
Replies
4
Views
2K
Peter_SSs
Peter_SSs
S
  • Solved
How to use a ComboBox with autocomplete and search as you type, but ignore blank formula or empty cells.
2
Replies
10
Views
4K
sijpie
sijpie
A
  • Question
Swapping Named Cell Ranges Using Drop Down List
Replies
4
Views
561
Akuini
Akuini
G
  • Question
How to sum values based on a drop-down list?
Replies
1
Views
390
oldbrewer
O

Forum statistics

Threads
1,214,789
Messages
6,121,605
Members
449,038
Latest member
Arbind kumar

Share this page

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