Data Validation - Switch Between List and Decimal

legendary_popsicle

New Member
Joined
Jul 25, 2011
Messages
49
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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
ABCDEF
1TasksTime SpentUser EntryTaskListTime
2Writing0.5Writing0.5
3Pictures0.25Pictures0.25
4Task A11Layout0.2
5Task B1.51.5Task A#N/A
6Pictures0.25Task B#N/A
7NewTaskNotOnListEnter time->
8Layout0.2
9Task BEnter time->
Sheet
#VALUE!
</td></tr></table></td></tr></table>
 
Last edited:

legendary_popsicle

New Member
Joined
Jul 25, 2011
Messages
49
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
ABCDEF
1TasksTime SpentUser EntryTaskListTime
2Writing0.5Writing0.5
3Pictures0.25Pictures0.25
4Task A11Layout0.2
5Task B1.51.5Task A#N/A
6Pictures0.25Task B#N/A
7NewTaskNotOnListEnter time->
8Layout0.2
9Task BEnter 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,550
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top