Help required - Data - Validation - List - Formula

Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<425E695F.6060205@contexturesXSPAM.com>...
> Perhaps you could enter the formula in an adjacent cell, then refer to 
> that cell in the data validation formula box.
> 
> amit wrote:
> > I am trying to generate "Drop down list" using formula.
> > 
> > The path I am using is :
> > 
> > Data > Validation ---List ----Formula
> > 
> > It seems there is limitation on no. of characters, which could be
> > typed in this FORMULA field.
> > 
> > Can someone help me in generating the list using someother technic.
> > 
> > Thanks in advance for your replies.


Debra,

Thanks a lot for your reply.

I tried that but it doesn't help for list of values.

More specifically:

I have 12 columns, each containing a list of 100 values (1 value in a
cell).

Based on the value of a cell, different from the cells mentioned
above, in range of 1 to 12 one of the list is to be selected in Drop
down list (using Data > Validation ---List ----Formula).

The above mentioned field at the end of path allows only 8
IF-THEN-ELSE type statements.

I tried using Index, Match, address functions etc. but I am not able
to get the solution.
0
awanjari (1)
4/15/2005 12:32:49 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
321 Views

Similar Articles

[PageSpeed] 10

As I replied at your post in microsoft.public.excel.worksheet.functions:

It sounds like you could use dependent data validation lists. There are 
instructions here:

   http://www.contextures.com/xlDataVal02.html

amit wrote:
> Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<425E695F.6060205@contexturesXSPAM.com>...
> 
>>Perhaps you could enter the formula in an adjacent cell, then refer to 
>>that cell in the data validation formula box.
>>
>>amit wrote:
>>
>>>I am trying to generate "Drop down list" using formula.
>>>
>>>The path I am using is :
>>>
>>>Data > Validation ---List ----Formula
>>>
>>>It seems there is limitation on no. of characters, which could be
>>>typed in this FORMULA field.
>>>
>>>Can someone help me in generating the list using someother technic.
>>>
>>>Thanks in advance for your replies.
>>
> 
> 
> Debra,
> 
> Thanks a lot for your reply.
> 
> I tried that but it doesn't help for list of values.
> 
> More specifically:
> 
> I have 12 columns, each containing a list of 100 values (1 value in a
> cell).
> 
> Based on the value of a cell, different from the cells mentioned
> above, in range of 1 to 12 one of the list is to be selected in Drop
> down list (using Data > Validation ---List ----Formula).
> 
> The above mentioned field at the end of path allows only 8
> IF-THEN-ELSE type statements.
> 
> I tried using Index, Match, address functions etc. but I am not able
> to get the solution.


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

0
dsd1 (5911)
4/15/2005 12:49:50 PM
Reply:

Similar Artilces:

copying a formula down wards
=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 Hi, Try this ARRAY formula. ARRAY enter it and it will return the first match, drag down for the second etc. It will return an error if there isn't a second match so you could wrap the whole thing =isserror(formula etc =OFFSET(IF(ROWS(B$7:B7)<=COUNTIF(Proj_code,$E$1),INDEX($F...

Data Selection keyboard shortcuts -Source Data Window
When making scatterplots in Excel 2000, is there a keyboard shortcut to select the data once you are in the Source Data window? This is a very large spreadsheet and standard keyboard data selection shortcuts don't work when the Source Data window is open. When creating the chart, keyboard shortcuts (e.g. Ctrl+Home; Ctrl+End; Shift, followed by arrow key) should work while you're in the Source Data page of the Chart Wizard. However, after the chart has been created, if you choose Chart>Source Data, you'll have to select the cells with the mouse. Carol Hackney wrote: >...

HELP
help need to use statement expense in excel ...

CISCO and MSExchange help needed
We need help with troubleshooting and resolving complex problems to ensure quality transmission service on the network; manage and maintaining and user accounts, maintaining and updating our system security plans on all platforms if anyone feels they can help us. We have sufficient budgets to pay for the right person. Thanks John. -- John D Allen. CEO & President. Leveridge Systems INC. ...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

data validation ?
I have two columns..I have written the below example to explain A B 1 a 2 b 3 c 4 d 5 e 6 f what I want to do is put a dropdown menu on a sheet, that when "1" is selected, in another cell, "a" would be displayed, and so on for all the way to "6"/"f" I have selected all of the numbers in the A column and name/defined it as "points". I have done the same with all in column B and names it "mm". I basically want someone to be able to select one of the points, and have it automatically display the mm value....any ...

use formula on external data range
Hi All, I've got a problem with data I read from a database. I wrote a VBA Sub in Excel 2007 with retrieves data via ActiveSheet.QueryTables.Add() per OLEDB out of a database. This works fine and puts the data in the range beginning on cell "A8" Now my problem: I have a formula '=SUM(A8:A10) in cell "B1". This formula always comes out = 0, although there are values in the cells. But if I edit cell "A8" without changing the value (just the alignment changes) it is recognized and the formula shows = 1 What am I missing here ? Thanks ...

Formulae in headers or footers
Is it possible to put totals in a header or footer? I haven't seen any place that says you can or can't. If you can, then how? Thanks, -- Lee Coleman You need VBA code to do that. If your data is in D1:D100, this will do as an example: Right-click the Excel LOGO near the file menu, select View Code, paste this in: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Total = " & Application.Sum(Range("D1:D100")) End Sub Bob Umlas Excel MVP "Lee" wrote: > Is it possible to put totals in a header or f...

Using Invoice template: overriding formulae
I am having a problem when using the Excel 2000 Invoice template. Sometimes I enter text into the 'Quantity' column. Then Excel is unable to calculate the Total, in the blue shaded area. My question is: How do I override the formula calculation in the shaded area to simply enter the numbers and/or text of my choosing? WTIA Andrew Stucken -- Evil flourishes when good men do nothing. Don'y know how to override the original protectiion. Easiest would be to copy and paste into new sheet and use this to create your own template. Roger "Enigmaman" wrote: > I a...

Need Help with log on
I have frontpage and have always edited my site using it, my computer crashed, we got a new one and installed frontpage again. In the past when I would go to edit my site a box would come up asking for my user name and password so I could connect to the host server (I think thas what it is called) Anyway now no box comes up at all and I do not seem to beable to edit anything. I have asked my web host and they can not help me. The site is up and working but I can not make changes. Sorry I do not understand much of the tech stuff. I had a lady who did this for me but she is no lon...

IF STATEMENT HELP NEEDED!!!
I'm trying to create a spread sheet and don't know this program AT ALL! I'm a designer and math and art just don't mix... So, here are the equations... In cell X6 I enter: If C6>D6 then (C6-D6)*5 or If C6<D6 then (D6-C6)*10 But, if cell D6 is yellow --- Put 20 in cell X6 So, I need some sort of identifier in the equation saying whether the cell is yellow or not. I'm sure excel doesn't recognize "yellow" but there must be another way. I can put a letter with a number in that cell if necessary. I'm not sure this is making sence, but if anyon...

SKU119.CAB for Publisher 2002 Help
I have msiplaced my installation CD...I have my product ID and Version number close by....but, can't access my publisher files for some reason. Says I don't have access because the program is not intalled for current user. I am the only user on this computer. And somehow....I just can't get in now. Can someone help me find it...email the file to me...or something? Ric S EventDyn@aol.com Thank you Have you tried to do a repair? -- JoAnn Paules MVP Microsoft [Publisher] "EventDyn" <EventDyn@discussions.microsoft.com> wrote in message news:6545BA3A-185D...

Help with 'Auto_Open' please...
SubThe first time I open my excel workbook, a macro automatically runs. Part of this macro eventually calls for the deleting of 'Sheet1' in the workbook. When the macro is finished running, I save the file. The second time I open the workbook, I want it to check to see if it contains a 'Sheet1'. If it does, 'Exit Sub', else do something else... I've pasted my VBA code below. Do you see anything wrong with it? Many thanks, Craig ---------------------------------- Private Sub Auto_Open() calcmod = Application.Calculation With Application ...

Statically Linking MFC DLL's
Hello, I'm having trouble statically linking the MFC DLL's to my application. At compile time I get errors C2039 and C2065: : error C2039: 'classEditorView' : is not a member of 'EditorView' : see declaration of 'EditorView' : error C2065: 'classEditorView' : undeclared identifier The line that the error points to is the following: IMPLEMENT_DYNCREATE(NVEditorDoc, EditorDoc) And FYI, NVEditorDoc is derived from the abstract EditorDoc, which is derived from CDocument. Noteworthy is the fact that I do not get these errors when I use MFC a...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

Consolidate data with text
Excel Consolidate Data does not consolidate text. I need to somehow combine data from several different worksheets into one (the sheets contain text and numbers). The tables on each sheet are set up identically with identical column headers. Ideally, I would like to import worksheet #1, then beginning on the first blank row after worksheet #1, import (or copy and paste) worksheet #2, etc to form one combined database. Ideas, please? I should have mentioned these worksheets are in different workbooks. Ron de Bruin has tons of code samples for combining data here: http://www....

Lists in excel
Is there a way to have a list that will eliminate a value once it is already used in a group of cells? Have a look here: http://www.contextures.on.ca/xlDataVal03.html -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Kameca" <Kameca@discussions.microsoft.com> wrote in message news:2B43DEBA-1BFD-457E-884C-6643C95081F1@microsoft.com... > Is there a way to have a list that will eliminate a value once it is > already > used in a group of cells? see: http://www.cpearson.com/excel/Duplicates.aspx -- Gary''s Stud...

Filter Custom List on Date
I have created a custom list with several date columns. I wish to create views based on filtering on a date being filled in or NOT filled in. Since Zero, and blank do not work, how do I do this? MOSS 2007 Greg DC you can filter it. make two conditions whatever you want like below "greater than" [today] or "is equal to" blank <-- no value entered. is it what you want ? "Greg DC" wrote: > I have created a custom list with several date columns. I wish to create > views based on filtering on a date being filled in or NOT filled i...

Excel 2003 question about help menu
I use Excel 2003 OFF-LINE. I find myself extremely limited and frustrated with the "menu approach" that it uses as well as the set number of items it will display as the result of a keyword search. The main problem for me ATM is that I know that the functions are categorized into say eight categories, e.g., text, but I don't always remember all of them. In the past, a search in Help would return a list of those categories from which I could quickly and easily navigate around to doublecheck my understanding. Search results in Excel 2003 are erratic, and trying to find the list...

Microsoft Word
I'm trying to get a list of all the files a Microsoft Word document is hyperlinked to. Is there a way to have Word generate or show a list of all the files a Word document is hyperlinked to? Thanks On Tue, 4 May 2010 14:50:01 -0700, ikovy <ikovy@discussions.microsoft.com> wrote: >I'm trying to get a list of all the files a Microsoft Word document is >hyperlinked to. Is there a way to have Word generate or show a list of all >the files a Word document is hyperlinked to? > >Thanks Use this macro (see http://www.gmayor.com/installing_macro.htm if...

Date Validation pop up
Hi Everyone, I was just asked to create a date validation pop up on a form that I'm designing. The validation is a pop up box that asks the user if the end date of the period is correct by taking the last date of the previous period and adding the number of days in the current period. So one field on the form asks for the end date of the last report period and another field asks for the number of days in the current period. For example the last day of the previous review period could be April 30, 2007 and the number of days in the current period is 7 days. So they would like a pop up t...

Changing format of Data Validation's Input Message
I'm using Data Validations. The cell has an input message, which appears by default as a rectangular bubble right under the cell. Is it possible to change the format of this bubble, to different colours, fonts, borders, etc? I want to make it more noticeable. I didn't see any formatting capabilities in that dialog. Hall wrote: > > I'm using Data Validations. The cell has an input message, which appears by > default as a rectangular bubble right under the cell. > > Is it possible to change the format of this bubble, to different colours, > fonts, borde...

teredo tunneling psuedo-interface code 10??? affecting my usb now i cant download music onto my ipod help!!
so i just got an ipod and when i pluged it in it said that and ipod was detected but could not be......( i forgot the rest) please unplug and try agian and it dosent work . so then i downloaded tune up utilities and it said i had 3 errors ,2 of wich really dont matter, but then i looked at the 3rd one and it said i had a code 10 on my teredo tunneling psuedo interface and it could not start. please help me!!!!! -- etfboy450 ------------------------------------------------------------------------ etfboy450's Profile: http://forums.techarena.in/members/169455.htm View this t...

trust help.
Hi all, We are going to create a two-way forest with CompanyB. The companyB has same internal and external public naming b.com. I just create the conditional forwarder for b.com to use their internal DNS server IPs. Now, when I ping b.com, it still routes to the external IP. Is this supposed to be? If I do nslookup b.com and it returns all their internal DNS IPs. Can I build a two-way forest trust or any additiona steps I need to configure? Thank you. If you are getting a different address when pinging and using NSLOOKUP on the same computer it is probably just the ...

User Form Data Validation
How, Please could someone point me to a good tutorial on data validation for user forms?. I have a workbook that uses the INDIRECT method to restrict options dependant on the first selection. I want to create a user form for data entry, if I use the Excel data form from the menu bar, the data validation is not carried across. A url to advice would be great, Thanks, Mickey Are you using a combobox on that userform? Instead of using =indirect() in the userform, I just used code to point at that other range. I put two comboboxes and two commmand buttons (ok/cancel) on a userform. Th...