Data Validation - Switch Between List and Decimal

[legendary_popsicle]

I wasn't even sure if this was possible, so I figured I would ask it here. I am building a form to track time spent on projects. In column A, I will have the name of the project and in column B I will have the point value associated with that project (.5 = 30 mins).

Example:

Column A Column B
Writing .5
Pictures .25
Layout .2

However, some tasks don't have a set time, so I'd like for some tasks to allow the user to input any decimal number. Is there a way to switch data validation between a list and "decimal," so that if they select a certain task from column A then it will either automatically populate column B with a value or allow them to fill it in themselves.

I am up for vba and non-vba solutions, but would prefer non-vba. All suggestions welcome!

 

[Jerry Sullivan]

This isn't the most elegant interface, but it's one way to accomplish this without VBA.

Add a Column C, which allows the user to enter values when the task in Column A has an #N/A time associated with it. The value "Enter time-> " comes up in Column B when this happens.

In the example below, the DV lookup table is in Columns E & F.

If your DV rules allow users to enter New Tasks that aren't on the DV list, this formula would work in the same way.

Excel Workbook
	A	B	C	D	E	F
1	Tasks	Time Spent	User Entry		TaskList	Time
2	Writing	0.5			Writing	0.5
3	Pictures	0.25			Pictures	0.25
4	Task A	1	1		Layout	0.2
5	Task B	1.5	1.5		Task A	#N/A
6	Pictures	0.25			Task B	#N/A
7	NewTaskNotOnList	Enter time->
8	Layout	0.2
9	Task B	Enter time->
Sheet

#VALUE!

</td></tr></table></td></tr></table>

 

[legendary_popsicle]

This isn't the most elegant interface, but it's one way to accomplish this without VBA.

Add a Column C, which allows the user to enter values when the task in Column A has an #N/A time associated with it. The value "Enter time-> " comes up in Column B when this happens.

In the example below, the DV lookup table is in Columns E & F.

If your DV rules allow users to enter New Tasks that aren't on the DV list, this formula would work in the same way.

Excel Workbook
	A	B	C	D	E	F
1	Tasks	Time Spent	User Entry		TaskList	Time
2	Writing	0.5			Writing	0.5
3	Pictures	0.25			Pictures	0.25
4	Task A	1	1		Layout	0.2
5	Task B	1.5	1.5		Task A	#N/A
6	Pictures	0.25			Task B	#N/A
7	NewTaskNotOnList	Enter time->
8	Layout	0.2
9	Task B	Enter time->
Sheet

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>

Thanks Jerry! This is definitely the direction I was going to go if I couldn't make Excel do magic. This is very helpful.