conditionally restricting values in excel

Hello,
I want to restrict values in a particular column based on the value in other
column. For eg.

if column A has value int then column B should be restricted to values
1,2,3,4,5
if column A has value bool then column B should be restricted to values true
and false.
if column A has value string I dont want any validation to
be done and the list box should not appear.
Is this possible.....

How do I achieve this ? Can I use macros to do this? How?

Thanks



0
jakal (5)
4/23/2004 12:03:29 PM
excel 39879 articles. 2 followers. Follow

2 Replies
790 Views

Similar Articles

[PageSpeed] 6

Hi
as a starting point:
http://www.contextures.com/xlDataVal02.html

>-----Original Message-----
>Hello,
>I want to restrict values in a particular column based on 
the value in other
>column. For eg.
>
>if column A has value int then column B should be 
restricted to values
>1,2,3,4,5
>if column A has value bool then column B should be 
restricted to values true
>and false.
>if column A has value string I dont want any validation to
>be done and the list box should not appear.
>Is this possible.....
>
>How do I achieve this ? Can I use macros to do this? How?
>
>Thanks
>
>
>
>.
>
0
frank.kabel (11126)
4/23/2004 12:20:43 PM
In a column in the workbook, enter the values 1-5
Select the cells, and name the range, e.g. IntegerList
There are instructions for naming ranges on my web site:
   http://www.contextures.com/xlNames01.html

In another column, enter TRUE and FALSE
Select the cells, and name the range, e.g. BooleanList

Select a blank cell, and name it NoList

Select the cells to receive data validation (e.g. B2:B100)
Choose Data>Validation
Choose to Allow: List
In the formula bar, type:

 
=IF(AND(ISNUMBER(A2),MOD(A2,INT(A2))=0),IntegerList,IF(ISLOGICAL(A2),BooleanList,NoList))

Click OK

If an integer is in column A, values 1-5 will be in the list
If a boolean is in column A, TRUE and FALSE will be in the list
If anything else is in column A, the dropdown arrow will appear, but no 
list. Users can type any value in the cell.


__jakal__ wrote:
> Hello,
> I want to restrict values in a particular column based on the value in other
> column. For eg.
> 
> if column A has value int then column B should be restricted to values
> 1,2,3,4,5
> if column A has value bool then column B should be restricted to values true
> and false.
> if column A has value string I dont want any validation to
> be done and the list box should not appear.
> Is this possible.....
> 
> How do I achieve this ? Can I use macros to do this? How?
> 
> Thanks
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/23/2004 11:58:09 PM
Reply:

Similar Artilces:

problem copying excel wroksheets
Hi all, when i try to create a copy of a worksheet from one workbook to another i get the following error message :- Microsoft Visual Basic file not found: 'C:\DOCUME~1\adonafee\LOCALS~1\Temp\VB37.tmp' can anyone help me out with this please???? Maybe cleaning up your temp folder will help. close excel windows start button|run %temp% Delete all those files and folders. It may be best to do this right after you reboot your pc, so that no other programs are running that need any of these files. == If that doesn't help, try disabling your antivirus software (just temporarily) ...

How do I get scalar value of a child stored procedure?
Hi, I'm trying to execute multiple stored procedures from a parent storedproc. One of the child stored procedures returns a value which I need for the next child stored procedure I need to execute in the parent stored procedure. I used the following syntax but looks like it's not the correct one. Could someone help me with the correct syntax? -- Execute child sp and get UserID DECLARE @UserID uniqueidentifier SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith' I appreciate your help with this. -- Thanks, Sam "Sam&qu...

That I do I cannot open to archives in Word nor Excel?
Would you please use this large open space to give us a better idea of your problem? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Antonio" <Antonio@discussions.microsoft.com> wrote in message news:EED7F92A-D1E4-40AF-98ED-2F6107301E4B@microsoft.com... > ...

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Import from Excel
I am running Outlook 2002 and I am trying to import addresses from excel. The addresses are in traditional excel form...A1:name, B1:address, C1:city, etc. It has worked, however, when I import each column under name, address, city, etc., each cell is getting listed under a new contact????? Does anyone know how I can get each address under ONE contact in outlook???? ...

Can t read mac-word files containing excel graphs on a PC
I am using microsoft Office X 10.1.4 on a powerbook G4 with operating system OS X 10.3.5 panther. I have created a graph in Excel on its own sheet (ie the one that contains the relevant data). I activate the graph, copy it to the clipboard, switch to Word and do a Paste Special, paste as image. So do I with all the excel graphs I want to insert into my word document (scientific report). The word document is saved under format "microsoft word document". I ve sent my word document to several colleagues who work on PCs. They all told me they could not see the excel graphs. They (the ...

Can't format numbers for France in Excel 2003
I'm using the US version of Excel 2003, but need to format costs, etc., for a project in the French euro format (e.g., I need 2050.45 euros to display as 2 050,45). That currency format isn't offered in my dialog box, and if I make a custom number format that will display correctly, it doesn't operate as a number. Any suggestions? On Wed, 10 Oct 2007 08:51:03 -0700, Mary Fran <MaryFran@discussions.microsoft.com> wrote: >I'm using the US version of Excel 2003, but need to format costs, etc., for a >project in the French euro format (e.g., I need 2050.45 euros...

Getting value from edit control?
How do I get the value from an edit control into its value CString variable? Also, how do I convert from CString to char* ? CString has an overload for LPCTSTR (which depends on whether you are compiled for Unicode or MBCS) so you can pass it pretty easily. If you need to access the buffer directly CString::GetBuffer() returns the pointer to the buffer. Call ReleaseBuffer() when you are done using that point (if you modify it). To assign and retrieve values with the edit control you should create a variable for the control and use UpdateData(). Open the dialog and right click on ...

when I open excel file,changes was undid-excel 2007, compatibility
I have a file that was made in excel 2007 working in compatibility mode and when I change format in some cell (for example font size), then I save changes and after reopen this fiel the changes was undid. Can some help me? Sorry for my english isn't very well -- thanks Pedro ...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Excel Queries using Access data
Im trying to create a query from within Excel. When I select new database query and select a database which has no securtiy login/password, no problem, works fine. However, when I try to source the data from a database which I have created using user logins/passwords it says that I do not have admin privilages to use the database even though I am putting in the correct login/username in the popup window? Please can anyone help? I've had limited experience with this sort of thing but I've always had to strip out the protection on the Access Database and create a copy before suc...

Excel To money
i have a ledger for my checking account in excel that i wish to convert to money format how do i do this ? In microsoft.public.money, WStoreyII wrote: >i have a ledger for my checking account in excel that i wish to convert to >money format how do i do this ? > See http://xl2qif.chez.tiscali.fr/xl2qif_en.php ...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Is there any way to do a rose diagram in Excel?
Is there a plugin or some way to plot a rose diagram in Excel 2003? In article <F6F21C0C-9297-4939-9153-C2AAA85F0A54@microsoft.com>, Jed@discussions.microsoft.com says... > Is there a plugin or some way to plot a rose diagram in Excel 2003? > Check the google.com archives of the XL newsgroups for several leads. Start with http://groups.google.com/advanced_group_search?q=group:*Excel* -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions ...

2007 excel autofilter change back to 2003?
Is it possible to change the autofilter in excel 2007 back to the way excel 2003 worked? I see you have a response to one of your other posts -- in a different forum. jonnybrovo815 wrote: > > Is it possible to change the autofilter in excel 2007 back to the way excel > 2003 worked? -- Dave Peterson ...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

"Functions Displaying", & Old Excel 97 ?
Hello, Really embarased to ask, but: Using an old copy of Excel "97" Where do I find the list of Functions that are available (on what pull-down menu), and how do I display them, please ? Just can't seem to find them on any of the items that displays. How can I get an icon to allow easy access to them on one of the toolbars ? Thank you, B. Excel's help is nice(?). Look for Functions. and... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's si...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Combobox value populate cell selection
I am looking for button code to have a selected cell range merged and populated with value chosen from combobox. This value is centered in the merged cell selection. The cells range is defined manually with mouse. Bart Have a look at this event code which you can refine. No error-checking for data in the selected range...........assumes the mergerange is empty when selected. Runs when a value is selected from Combobox1 Private Sub ComboBox1_Change() Set srng = Application.InputBox(prompt:= _ "Select A Range", Type:=8) With srng .HorizontalAlignment = xlCen...

Restricting the stationary
I'm running Exchange 2003 with Outlook 2003. I want to restrict users to HTML e-mail, my own stationary and only a handfull of fonts. Can this be acheived? You will have to purchase Microsoft Office System Babysitter Server 2007. Seriously, I don't know the answer to your question, but I believe the answer will more likely be obtainable from an Outlook newsgroup, and your solution, if any, will involve the use of the Outlook Custom Installation Wizard and the Custom Maintenance Wizard. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" ...

Relative, absolute and mixed values
How do I set a default for relative, absolute or mixed values so that I don't have to toggle through the F4 function each time? ...

Excel VBA programmer needed
Programmer needed visual basic, in excel I own a bee removal company . I keep most my records and calcualtions in excel and I am looking for an excel specialist / vba programmer that can write code to simplify and speed up some of the time consuming tasks. Time is a factor for me, money is less of a factor. I imagine it would be around 80 hrs work. With probable future changes and or additions that would most likley arise. Example: Bee removal work can be divided into two parts, 1 exterminating a hive, and 2. removing the honey from the structure (from 5 to 300 lbs). The field rep exterm...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...