stumped on creating dependent data validation lists

I created 2 Data Validation lists with Indirect functions. On trying
to use them in conjunction with a 3rd list, i realized my logic in
trying to accomplish what i need had been flawed. The problem is that
the 3rd list, which should be dependent on the 1st 2, changes
location.
That is, i have a very busy worksheet. In the main body of the
worksheet, there are no empty rows and columns. But almost every cell
needs to have dependent validation lists to select items from. The
main body is from A13 to AW131. In the area E14 to Q131, I have the
potential to need a dependent dropdown list in alomst every cell. i
tried putting the 1st 2 lists in an open area in rows 10 & 11, but i
soon discovered that the reference cells i entered in them using the
indirect function were not relative, and stayed the same no matter
which cell in the body needed to depend on them. After much work
creating named lists and working out the "indirect" formaulas, i
realized that when i selected the 1st list, ther was no way for Excel
to know which cell below to reference. I think what i want to do is
not possible, yet, i have over time received some incredible advise in
this group, so I figured that just because it seems impossbile to me,
it might be very possible to some of the users in this group. Or,
perhaps it can be accomplished with combo boxes?

Thanks,

Tonso
0
Tonso
4/15/2010 8:14:34 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
807 Views

Similar Articles

[PageSpeed] 23

hi

take a look at
http://www.contextures.com/xlDataVal15.html

--
Regards
Roger Govier

Tonso wrote:
> I created 2 Data Validation lists with Indirect functions. On trying
> to use them in conjunction with a 3rd list, i realized my logic in
> trying to accomplish what i need had been flawed. The problem is that
> the 3rd list, which should be dependent on the 1st 2, changes
> location.
> That is, i have a very busy worksheet. In the main body of the
> worksheet, there are no empty rows and columns. But almost every cell
> needs to have dependent validation lists to select items from. The
> main body is from A13 to AW131. In the area E14 to Q131, I have the
> potential to need a dependent dropdown list in alomst every cell. i
> tried putting the 1st 2 lists in an open area in rows 10 & 11, but i
> soon discovered that the reference cells i entered in them using the
> indirect function were not relative, and stayed the same no matter
> which cell in the body needed to depend on them. After much work
> creating named lists and working out the "indirect" formaulas, i
> realized that when i selected the 1st list, ther was no way for Excel
> to know which cell below to reference. I think what i want to do is
> not possible, yet, i have over time received some incredible advise in
> this group, so I figured that just because it seems impossbile to me,
> it might be very possible to some of the users in this group. Or,
> perhaps it can be accomplished with combo boxes?
> 
> Thanks,
> 
> Tonso
0
Roger
4/15/2010 8:31:39 PM
Reply:

Similar Artilces:

how do I tabulate data in excel 2003
I need to tabulate some data from 500 surveys with 30 questions. How do I do that? On May 13, 11:36=A0am, Becky <Be...@discussions.microsoft.com> wrote: > I need to tabulate some data from 500 surveys with 30 questions. How do I= do > that? How is your data organized? You start by pretending we cannot see your worksheet and its layout. Then you provide some details. Gord Dibben MS Excel MVP On Thu, 13 May 2010 11:36:01 -0700, Becky <Becky@discussions.microsoft.com> wrote: >I need to tabulate some data from 500 surveys with 30 questions. How do...

How to use data on two forms
I have two forms. A button on form1, and when i click on button1 my form1, form2 is open. Add some data to form2, and write it on form1, all with class. How to do that? imarkic <imarkic84@gmail.com> wrote in news:009b8c57-bc47-409a-8f3a- e995e1944f19@j5g2000yqm.googlegroups.com: > I have two forms. A button on form1, and when i click on button1 my > form1, form2 is open. Add some data to form2, and write it on form1, > all with class. > How to do that? > This really depends on the model you are using. In general, you will not control it from an outside c...

Folder List does not display files and folders
Under View, Page is selected with Toggle Pane set to Folder list. No folders or files are displayed. There are files in the default folder called My Web but I'm not able to see these in Folder List. Please can you advise? Thank you in advance. Have you opened a website using File->Open web or File->Open Site ? The file list will be blank until a website is opened in FrontPage. The folder My Documents\My webs is a website. -- Ron Symonds Microsoft MVP (Expression Web) http://www.rxs-enterprises.org/fp Reply only to group - emails will be deleted unread. ...

Trace dependents not working
I am able to do trace precendents from cells in several sheets to cell A. But when I tried to do a trace dependent on cell A, the trace dependent could not fine any formulas that refers to the active cell A. ...

Opening .dat files created by Excel for Mac
I createsd an Excel workbook on a Mac and emailed it to my self. The atchment is .dat (but that may have just been added by my email program as macs don't have file extensions, Ithink). I cannot open it in Excel XP running on Windows XP. I tried renaming it .xls and various other excel extensions but it still tells me that it dosen't recognize the file. If I open it in a text editor, I can clearly see many of the custom number formats, names, lables, and some cell values I used in the file, as well as formating for a graph I encluded, the name of the worksheets and meaningles...

creating a 2nd email account
We are currently running Exchange 2000 on a Windows 2000 server. I've been tasked to create an email account for our training department. They want the email account to be computer.training @(our company name). Do I need to create a computer.training account in AD in order to create an email box for this account or can I just assign this new email account to one of our trainers as a secondary email account? If so, how do I do this? Jim you will need to create an AD account...then you can grant permissions to the mailbox, and if you like, disable the AD account... "Jim in Cl...

how to create relationship between Account and Contact relationshi
Hey all- I'm writing an application that creates accounts in CRM 3.0 automatically using the web service. So far, so good. The problem I'm having is programmatically assigning accounts to the contacts. If my accounts are already created in CRM, I should just be able to grab their guid and assign it to the contact using contact.parentcustomerid, right? Well, if that's the case, what should the code look like? How do I assign values to the type of Customer? If that's not right, can someone point me in the right direction? I got this sample code from one forum but it doe...

Copy formulas without data.
I have a complex spreadsheet that has many formulas and much data. It was not well designed, but I inherited it. Now I have to start the new fiscal year and I want to delete all the data, but leave the formulas, headers and such intact. Is there a way to do this? Deleting cell by cell is going to be a big project. ---Mike You should be able to hit Edit-->Go to-->Special-->Constants. Then hit your delete key. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Praising Jesus" <1611kjb@earthlink.net> wrote in message news:elOCVp$gFH...

Row Height dependent upon PC's font scale
I made a label template that only puts the text on the label if the row heights are correct. However, when the template is used by other people, the text and labels don't line up right. The row height changes scale dependent on each PC's font scale specified on its desktop. Is there any way to specify row height absolutely, instead of only being able to adjust it in pixel increments? Thanks. ...

2004 Office pros+cons of moving Microsoft User Data to 2nd partition? Do any MVPs?
With Office 2004, are there any reasons why one should NOT alias the Microsoft User Data folder, leave the alias in place on the boot partition and move MUD to another partition? Do any of the MVPs do this? Respectfully, Norm Hi Norman, It's people like you who make me stop and think every once in a while about what I am doing. I have a MUD folder at work, another at home. But this is not really the best solution for me. What I should really do is have a single master MUD folder someplace, probably on a network drive that's backed up every day. Then I should sync to local MUD fol...

How do I create flash cards?
I would like to create flash cards, (in Publisher?), and I could not find any such template. I can, of course, put the info I want in a blank doc and print it onto 3 x 5 cards, reversing them to get the back printed properly. But I was hoping that there was a simpler way. I can not be the only person who wants to do this! Thanks for any help, There are flash cards on this site that might help. There is a "make your own" section. http://www.abcteach.com/directory/basics/flashcards/ -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.micr...

Create macro not available
I'm trying to create a macro from scratch, and when I select Tools, macro, and go to the dialog box, the "Create" button is grayed out. What would prevent me from being able to access this feature? I checked to make sure the worksheet isn't hidden (I know this can prevent editing, not sure about creating). I'm using XL2000. Any ideas? TIA dave Type a name for your macro in the "macro name" box. Create will un-gray itself. Gord Dibben Excel MVP On 27 Jan 2005 10:48:13 -0800, "davegb" <davegb@safebrowse.com> wrote: >I'm trying to cre...

creating a dropdown list in excel
i want to create a dropdown list in excell that will highlight a word when first letter is typed To make a dropdown first highlight the list you wish and give it a name (anthing you want eg listX). Do this by going Insert>name>create. After doing this go to the cell where you wish for the dropdown list to appear, and go Data>Validation. When the box comes up, in the box next to "allow:" secect "list". In the box below that says "source" type "=(your list name). For example "=ListX" Your dropdown is then complete, so click ok. hope ...

lines between data points
I created a chart in an earlier version of Excel (probably Excel 97). The Chart has one data series ploted as a line chart with data points and lines between the datapoint visible. In the original version of the chart I was able create a break in the line by simply turning off the line for a particular selected data point. When I recieved Excel 2002, the charts I had created earlier looked different. Now instead of a break in the line, there is a line which points from the last datapoint with a line to the next data point with a line, skiping any datapoints that have thier lines d...

Creating/modifing Outlook Rules from Excel
Does anybody have an example of how to create/modify a rule from excel? What I want to do, is once I make a contact with somebody, flag their email address so that when I get email from them, it will move it to a special folder. Thanks, Bruce I don't know how to to that from Excel, but to do it from Outlook is relatively simple. Did you mean to specify Outlook? >-----Original Message----- >Does anybody have an example of how to create/modify a rule from excel? >What I want to do, is once I make a contact with somebody, flag their email >address so that when I get emai...

SumIf Question? If question? I'm stumped..
I have three columns. "A", "B", and "C". I want to sum column "C" when "A" equals a specific value AND "B" equals a specific value. For example, sum the amuunts in column "C" when "A" equals "ball" AND "B" equals "bat". Any ideas? -Adam You can use an array formula, you need to press CTRL+Shift+Enter after typing the formula, if you've done this correctly curly brackets {} will appear around the formula in the formula bar: Use a formula something like: =SUM(IF(($A$2:$A$...

Format of downloaded data
> A data provider enables me to download real estate data that includes a column of sale prices in dollar amounts. > > The search through Internet explorer shows a page of data which can then be > downloaded to Excel. However the dollar items come into Excel as labels > rather than numbers which then prevents me from manipulating this data. > > This only happens when I download the data with my office desktop computer. > When I download it at home or on my laptop the data comes through as numbers > not labels. This suggests that I have a setting which is incor...

how do I create a cluster chart?
How do I create a stacked cluster chart in Excel? Jon Peltier's instructions should help you. http://www.peltiertech.com/Excel/Charts/format.html#ClusterStack "JoanH" wrote: > How do I create a stacked cluster chart in Excel? JoanH, There can be several types of clustered charts in Excel. For example, you can have a clustered column which consists of a series of columns. Or, you can have a series of bars, which consists of horizonal bars. Finally, there are a number of combination charts that could be considered clustered. To create a simple clusted column cha...

I am stumped
When I sign on to messenger I always get that some one is trying to add me it is always some one I don't know and when I go to their profile they have NO Name and no profile how can I stop these invites.I just keep pushing Block. I know I had stopped once but I got a new computer and forget how to stop these Please Help Thank you. Greetings Lana, These are spam. Unfortunately there isn't a lot you can do about it right now besides declining the invites. -- Jonathan Kay Microsoft MVP - Windows Live Messenger MSN Messenger/Windows Messenger MessengerGeek Blog: h...

how to create an excel template
I am having problems trying to create an excel template. I took a xlt file. and put it in the dir C:\Program Files\Microsoft Office\Templates\1033 However I do not see it ever showing up in the file>new>on my computer is there something special you have to do? thanks Some different computers and versions have slightly different locations. Let XL automatically place it in the proper location for you by simply clicking: <File> <Save As> And, at the bottom of the window, expand the "Save As Type" box, and click on "Template (*.xlt)" Then <Save>...

Unable to create data file
I'm running Outlook 2002 SP-2. Sometimes when I want to create a .pst file, I get the message box "Unable to create data file". There is no message number or any other identifiable (searchable) information in the text box. I can sometimes get around this problem by shutting down Outlook and restarting, or launching some other applications (such as Explorer); suddenly the problem disappears and there is no problem in creating a new data file (FILE/NEW/OUTLOOK DATA FILE). There is plenty of disk space available. Any clue as to why this problem sometimes arises? ...

[OL2003] Rule to create folder and move based on email ?
Is it possible to create a rule that would move incoming email to a subfolder having email address as name, and if no such folder exist, create first the folder ? abc@com.net --> Create subfolder ABC@COM.NET and move email in it 123@net.dot --> Create subfolder 123@net.dot and move email in it abc@com.net --> Move email in subfolder abc@com.net abc@com.net --> Move email in subfolder abc@com.net etc. ...

Drop list and redirect
I have two workbooks W1 and W2, they both contain the same columns with the same type of data. I know how to have W2 display the changed cell when W1 is updated, however I would also like to add a validation list for W2 in the same cell. The list contains the same values as those entered in W1. Is there any way to put the validation drop list and a redirect in the same cell and if so how??? Your ever greatful student..... I may not have a full grasp of what you want to do, but this is what came to mind. Name the list in W1 and in the W2 valadation drop down source box ent...

Creating schedules
I am the CAD Manager for a major retail electronics company. I work in the Architectural & Engineering department. I am trying to locate a software package that can be programed to look at certain fields in our access database and usning formulas create a work schedule of when projects are to be completed. To be more specific if I want to know what construction drawings need to be completed between July 1 and August 25 it will look at the construction start date in Access and calculate (backs off 90 days) and will schedule when the particular jobs should be completed. Can thi...

Possible to 'copy' cell data into another cell?
If for example A1 contains: hello B1 contains: you Is there a equation/command to put in cell C1 to get it to copy info from other cells i.e. so C1 shows: hello you ???????? =a1 & " " & b1 If you decide you want money or dates, you can use something like: =a1&" "&b1&text(c1,"mm/dd/yyyy") Dave wrote: > > If for example > > A1 contains: hello > B1 contains: you > > Is there a equation/command to put in cell C1 to get it to copy info > from other cells i.e. so C1 shows: hello you > > ??????...