Excel f(x)s = Excel Functions
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:
Creating a Data Validation based on another Data Validation
Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it.
You need to create two names (Formula > Name Manager), one has this formula
=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2))
The other one has this
This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox. The screenshot almost says it all. You need 4 steps: 1- Create the name for a column in your Excel file, go to Names > Define in Excel2003 or earlier, or go to Formulas >…
