Excel 2013 - Data Entry Form with Data Validation Columns

[beadedbytes]

I’m currently using Excel’s data form feature to keep track of multiple projects and the corresponding tasks / hours spent on each task.

Also, I’m using the data validation feature in order to be consistent in assigning the right project and task names for each data entry.

However, as each new entry is added to the data form list, the data validation for project and task columns does not expand to each new entry line.

Any suggestions on how to get the data validation to expand to each new entry in the particular column (i.e. project and task names)?

Thanks in advance for feedback.

 

[SkipVought]

Hi,

Are you using the Structured Table feature? Data > Tables > Table.

 

[SkipVought]

I reviewed your post and have some questions.

When you state, "for project and task columns" are you implying that your lookup table for tasks has multiple columns?

 

[beadedbytes]

Hi, Skip,

Question #1 Answer:
No, I'm not using Excel's structured table feature.

Question #2 Answer:
Here's the sample table on Sheet1 in the Excel workbook --

Date	        Project	Task	TaskType	Hrs	Comments
11/17/2014	Proj1	Task5	Billable	1	aaaaa
11/17/2014	Proj2	Task1	Unbillable	3	ccccc
11/18/2014	Proj3	Task1	Billable	2.5	fffff
11/19/2014	Proj3	Task2	Billable	2.5	
11/19/2014	Proj3	Task1	Billable	2.5	bbbbb

The data validation values for Project, Task & TaskType are kept on a separate sheet (Sheet2) in the Excel workbook. Separate data validation lists were created for each item -- 'Project' values are in column A2:A4; 'Task' in column B2:B6 & 'TaskType' in column C2:C3.

Let me know if you need more information.

 

[SkipVought]

Project values A2:A4
Task values B2:B6
TaskType C2:C3

This is mystifying. Does not each Project have its own set of Tasks. And is not each TaskType associates with a unique set of Tasks? If so, your table defies the reality!

 

[beadedbytes]

Skip,

"Does not each Project have its own set of Tasks." -- Yes

"And is not each TaskType associates with a unique set of Tasks?" -- If I understand your question, Task Type for each Task is either 'billable' or 'unbillable'.

I'm gathering from your response my table should in fact expand that data validation as new data entries are created?

V.

 

[SkipVought]

[pre]
Project Task TaskType
Proj1 Task5 Billable
Proj2 Task1 Unbillable
Proj3 Task1 Billable
Proj3 Task2 Billable
[/pre]

So this is what I'd see as the Project/Task table. In my world of manufacturing it might be the operations and work centers for parts, or in a bakery it might be the ingredients for a recipe. This is what you begin with.

1) generate a list of unique projects as the source for you Project Data Validation list.

2) given a specific project, generate a list of unique tasks for that project as the source for your Task & TastType Data Validation lists.

That's the drill, if I understand your situation.

 

[SkipVought]

Now back to the original question:

"However, as each new entry is added to the data form list, the data validation for project and task columns does not expand to each new entry line."

Make your data form table a Structured Table as specified in my first post. Formulas and formats will propagate as you add rows. See Excel HELP for details & features.

 

[SkipVought]

Sorry, to create Structured Table

Insert > Tables > Table

 

[beadedbytes]

Hi, Skip,

I've added a 'structured' table to my data (Excel 2013 using Insert >> Table selection). And, yes, the Data Validation list remains intact when manually entering new rows of data.

One question. Is there a way to get the Data Validation values inside the Data Form feature so that the 'valid' selection for 'Project' can be selected inside this data entry feature?

Thanks for your help,
V.

 

[SkipVought]

I'm not clear on what table you're using the Data Form feature?

And why are you using this feature?