Data Validation

[Steph]

I'm not sure if this is a VBA question or not, but here goes.
A workbook has a table on Sheet1 that consists of 2 columns and hundreds or
rows. Column A is Poject Name, and column B is Tasks.

On sheet 2 w have a timesheet template where employees must pick a project
and task to charge time to. We have data validation set up on both columns
with list pulldowns on each to make sure they pick a project & task that is
included in the list.

Can we narrow that Task pulldown based on their selection of project?

ie if someone picks project 12, then the Task pulldown will only include
tasks that are related to project 12. Possible?

Thanks in advance......

S

 

[Stephen Rasey]

On Sheet1, Are your two columns independent of each other or are they row
wise pairs?

In other words, In column A you have projects. In Column B, do you have
Tasks or Tasks for the Project in Column A? Are there the Same task in
Column B for different projects?

If yes, then I think you have a fairly simple solution.

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.

Stephen Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org