Data Validation List or text

[Tempfor]

First Post -

I'm trying to fill Column C with a disposition for a request.

Right now, if the ship date is more than 45 days from today, column B supplies "N". If B is "N", I'd like to populate C with "Decline". If column B is "Y", provide a list for a table. Data Validation doesn't seem to like it.


Data Validation is =if(B2="N","Decline",List)

[TABLE="width: 100"]
<tbody>[TR]
[TD]Ship Date[/TD]
[TD]Within 45 Days?[/TD]
[TD]Accept/Decline[/TD]
[/TR]
[TR]
[TD]1/1/1988[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/19/15[/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your ideas!

 

[ranman256]

ok, but you cant put a list in a cell. What do you want in the cell? A hyperlink to the list?

 

[Fishboy]

First Post -

I'm trying to fill Column C with a disposition for a request.

Right now, if the ship date is more than 45 days from today, column B supplies "N". If B is "N", I'd like to populate C with "Decline". If column B is "Y", provide a list for a table. Data Validation doesn't seem to like it.


Data Validation is =if(B2="N","Decline",List)

[TABLE="width: 100"]
<tbody>[TR]
[TD]Ship Date[/TD]
[TD]Within 45 Days?[/TD]
[TD]Accept/Decline[/TD]
[/TR]
[TR]
[TD]1/1/1988[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/19/15[/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your ideas!

Hi Tempfor, I don't know of a way to make it happen automatically, but it is possible to build data validation based on either the Y or N being in column B.

First, somewhere you need to create a table with the possible values for both Y and N as my example below:

Excel 2010
	A	B
1	Y	N
2	List1	Decline
3	List2
4	List3
5	List4
Sheet2

With the list in place, (using my example as reference), select A2:A5 then in the name box to the left of the formula bar type in Y and press enter.
Next select B2, then in the name box to the left of the formula bar type in N and press enter.

You now have 2 validation lists, Y and N.

Now go you column C, select Data Validation, select List, then enter the following formula:

=IF($B8="N",N,IF($B8="Y",Y,""))

This can be drag-filled down to copy the validation rule down the column.

What it basically does is

IF Column B = "N" display the "N" data validation list (only has decline as an option)
IF Column B = "Y" display the "Y" data validation list (whatever options you put in your Y list)
IF Column B = "" no options are available

Excel 2010
	B	C
7	Your Column B	Outcome
8
9	N	Decline
10	Y	List3
11	Y	List4
12	N	Decline
Sheet2

I do not know of a way to make this automated as you cannot have formulas AND data validation lists in the same cells (as far as I am aware)

 

[Tempfor]

ok, but you cant put a list in a cell. What do you want in the cell? A hyperlink to the list?

Sorry, "List" actually is an indirect reference to a list of possible options.

What I'm trying to do is a bit like the solution given to the first example here, but instead of "partial-list-range", I'm looking to provide "Decline" automatically.

Thanks.