Help! Combining Validation and Formula

Big Blue

Board Regular
Joined
Mar 24, 2002
Messages
52
I would like to do the following:
if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

I bow down to your expertise...........
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
On 2002-04-03 11:53, Big Blue wrote:
I would like to do the following:
if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

I bow down to your expertise...........

Probably not quite what you want, but you could X in some cell say in a sheet called Admin. Select this cell and name it List1. Put underneath this X-cell, Y and Z. Select the cells with Y and Z and name the selection List2.

In data validation that you set up in B5 opt for List for Allow and enter the following formula:

=IF(A1=0,List1,List2)

List1 will be active if A1 is empty or houses the number 0. If a real 0 must be the value of A1 for X, then use:

=IF(AND(LEN(A1)=0,A1=0),List1,List2)

Aladin
 
Upvote 0
So close! Thankyou!
My problem is that if the user doesn't remember to fill in cell a1,(ie a1 =0) I want cell b5 to default to X automatically. If the user does fill in cell a1, then I want to offer dropdown list1 in B5.

Your solution is great, but it requires user to enter default in B5 by selecting from "other" list.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top