Dynamic selection list

Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas

Can also be used as form to read from a large table in another sheet

 

After creating two names, one is “Months” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1)

And the other one is “Month_All” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3)

Draw a listbox using “Developer toolbar”, then make its input list as “Months” name we did above, then use these formulas:

=INDEX(Months_ALL,E4,1)
=INDEX(Months_ALL,E4,2)
=INDEX(Months_ALL,E4,3)

To lookup the other fields once you select from the listbox

You can create a combobox from that “Developer toolbar” with exactly same features

Comments

comments