From microsoft.public.excel.programming 12-aug-2004
Sheet1 has a Project Column and a Task Column.
Sheet 2 is a timesheet template. There are pull downs for Project
and Task.
Can we have the Pull down for Task list only the Task for the project
Selected?
--------------------------------------------------------------------------
In column A you have projects. In Column B, you must have for
the Project in Column A
There may be same task in Column B for different projects
Have your Sheet1 sorted in order of Project. Tasks can be in any desired
order within the block for one project.
The pull down for Project will set the value in the Project field of the
form. Lets call this a range named: ProjectSelected.
On Sheet1, you have a range called ProjectFirstRow out of the way somewhere.
it has the formula =Match(Sheet1!$A:$A, ProjectSelected,0)
Define a Name called TaskBlock
=offset(Sheet1!$B$1, ProjectFirstRow-1,0, 30,1)
This will create a named range that starts column B at the first row of the
selected project and extends for 30 rows. You can change 30 to some other
number, like the maximum number of tasks for a project.
Make your form validation for Task refer to the named range TaskBlock
This simple technique at least starts the Task Pull down with the first task
listed for the project. The 30 rows can be made to change with the number
of Tasks in a project, but that is another level of sophistication. Maybe
you can do a CountIF() on the Project name.
