Data Validation Dependant Lists

Hi there,

Is it possible to 'source' data validation/dependant lists  from 
seperate worksheet within the same workbook.  I know it is possibl
when using only data validation lists, but when I try to sourc
dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel say
this is not allowed??  

Kind regards

And

--
Message posted from http://www.ExcelForum.com

0
9/12/2004 9:52:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
576 Views

Similar Articles

[PageSpeed] 18

If you give that range a name, you can use it in the Data|validation dialog.

Debra Dalgleish explains it at:
http://www.contextures.com/xlDataVal01.html#Name

"andyp161 <" wrote:
> 
> Hi there,
> 
> Is it possible to 'source' data validation/dependant lists  from a
> seperate worksheet within the same workbook.  I know it is possible
> when using only data validation lists, but when I try to source
> dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel says
> this is not allowed??
> 
> Kind regards
> 
> Andy
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/12/2004 10:40:26 PM
Dave,

Thanks for your response.  I have actually been using Debra's site to
learn about data validation.  However, from my understanding she does
not explain how to source dependent lists from seperate worksheets. 
She does explain that by naming ranges it is possible to source basic
data validation lists.  However, her section on dependent lists says
that "In the Source box, type an equal sign and INDIRECT function,
referring to the first data cell in the Category column:
=INDIRECT(A2)".  When I have tried substituting (A2) with the name of
my category column, the dependent list will only give options to one
particular category, irrespective of what category I have chosen.

Kind regards

Andy


---
Message posted from http://www.ExcelForum.com/

0
9/13/2004 5:52:47 AM
There's no reason you can't use lists on different sheets - you just 
need to name your range that contains the first list. For example, name 
the cell with the category selection, say, "Source". Then on a different 
sheet, set the validation to

Allow:      List
Source:     =INDIRECT(Source)

See

    ftp://ftp.mcgimpsey.com/excel/andyp161demo.xls

for an example





In article <andyp161.1cic1x@excelforum-nospam.com>,
 andyp161 <<andyp161.1cic1x@excelforum-nospam.com>> wrote:

> Dave,
> 
> Thanks for your response.  I have actually been using Debra's site to
> learn about data validation.  However, from my understanding she does
> not explain how to source dependent lists from seperate worksheets. 
> She does explain that by naming ranges it is possible to source basic
> data validation lists.  However, her section on dependent lists says
> that "In the Source box, type an equal sign and INDIRECT function,
> referring to the first data cell in the Category column:
> =INDIRECT(A2)".  When I have tried substituting (A2) with the name of
> my category column, the dependent list will only give options to one
> particular category, irrespective of what category I have chosen.
0
jemcgimpsey (6723)
9/13/2004 8:52:54 AM
Andy

Yes, you need to name your list (range), for example 'mylist', then select 
list from the options in data validation and type =mylist to identify the 
list.

"andyp161 >" wrote:

> Hi there,
> 
> Is it possible to 'source' data validation/dependant lists  from a
> seperate worksheet within the same workbook.  I know it is possible
> when using only data validation lists, but when I try to source
> dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel says
> this is not allowed??  
> 
> Kind regards
> 
> Andy
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
canary (2)
9/13/2004 10:07:11 AM
In the sample workbook for this page:

   http://www.contextures.com/xlDataVal02.html
      (the link is at the bottom of the page)

the lists are on a separate worksheet.

The reference to "the first data cell in the Category column" is the 
first cell in which you've selected a category, not the list of categories.

andyp161 < wrote:
> Thanks for your response.  I have actually been using Debra's site to
> learn about data validation.  However, from my understanding she does
> not explain how to source dependent lists from seperate worksheets. 
> She does explain that by naming ranges it is possible to source basic
> data validation lists.  However, her section on dependent lists says
> that "In the Source box, type an equal sign and INDIRECT function,
> referring to the first data cell in the Category column:
> =INDIRECT(A2)".  When I have tried substituting (A2) with the name of
> my category column, the dependent list will only give options to one
> particular category, irrespective of what category I have chosen.

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

0
dsd1 (5911)
9/13/2004 11:06:42 AM
Reply:

Similar Artilces:

names in the distribution list not sorted when emailed
Hello, I have Office XP 2002 with SP2, Windows 2000 Pro. SP3. I have this problem, when I send an email to a distribution list containing the contacts ( a, b, c, d, e) and I go to the Sent Items folder, I found the message was sent to "e, a, b, c, d" and after I did my tests I found that the receipients can see that it was sent to e,a,b,c,d. In other words, it is taking the last contact and putting it as the first. I dont want that to happen, I just want them to appear the same way they are in the distribution list. any help would be highly appreciated. ...

chart with two data series and two colors for each data series
Hi! I am using visual studio.net 2.0 and trying to use OWC11 to draw a chart from the database. If its only one data series it is fine but i cant see the second data series. all i was trying to do was display actual and budgeted against year. below are the code for clarification gvPortfolioHisex.DataSource = dtPropertyCapex gvPortfolioHisex.DataBind() 'Now Build a graph from the dataset Dim oChartSpace As New Owc11.ChartSpaceClass() Dim objChart As Owc11.ChChart = oChartSpace.Charts.Add(0) objChart.HasTitle = True objChart.Title.Caption = "Historical Expenditure Graph&quo...

Repeating Data Queries
Hi All I am new to .Net and am battling with a very simple query. I am pulling an image path from a SQL database using SqlDataSource. I use the control once at the top of my page. I then repeat this query 3 times in my code using asp repeater with the DataSourceID pointing to the same SqlDataSource. It all works perfectly except for one thing. I am trying to pull the image paths in random order so every time the page reloads you see a different image that when you click on it enlarges. My code displays perfectly but does not match the enlarged picture as every time I use the repea...

Data Migration Pack extremely slow
We are trying to import aproximately one million records from external custom applications. We have copy all the data into de appropiate CDF tables but the problem is that when we run migration, it is extremely slow. We get a performance of 4 records/second so the complete migration runs for about 70 hours ! We are using SQL Server 2005 64 bitsas the database server. The machine has an AMD Opteron dual CPU with 8GB RAM. Hard drive is SCSI 15.000 rpm. Has anyone experienced similiar problem? Any idea about improving performance. Thanks Data migration can be slow. The speed varies across ...

"Insert Copied Cells" dysfunctionality when data is filtered
In Excel 2003 spreadsheet with some hidden columns, I've applied some filtering using Data->Filter->AutoFilter. I tried several approaches to replicating an entire row. Approach#1 ------------------ I selected a row by clicking on the row index number on the left edge. I pressed Ctrl-C to copy the row. In the following row (which is consecutively numbered), I tried right- clicking and doing "Insert Copied Cells". That option was not available. Approach#2 ----------------- I inserted a blank row in the place where I wanted the copied row to be copied. Then selected th...

Selecting highlighted data from one column and placing them in another column
Hi, I have data in Column A. Some of the rows in Column A are highlighted in yellow. I would like to copy the highlighted data and paste them into Column B. For Example: Column A 2342 1234 2121 5675 2321 Let's assume that 2342 and 2121 are highlighted. So my resullt in Column B will be: 2342 2121 Keep in mind that I have n numbers of data in Column A. Try this Macro: Sub Colour() Dim bCol As Long Dim x As Long bCol = 2 For x = 2 To 1000 'increase as required If Cells(x, 1).Interior.ColorIndex > 0 Then Cells(bCol, 2) = Cells(x, 1).Va...

Weekly incremental data
Hi, I am having trouble understanding if MS ACCESS supports reporting on a weekly basis, based on data that is delivered in a incremental file (updated weekly) where data travels through status changes based on date fields that are populated. I will give an example. FILE1: WEEK 3 - current status report (date 15-jan) rec_id | prod_date | trans_date | deliv_date | calculated status 1 1-jan 4-jan 6-jan | delivered 2 1-jan 6-jan 12-jan | delivered 3 8-jan 12-jan | on transport 4 ...

Recently used file list. It is set for 9, but the list seems to be cleared
I followed the Help instructions. Tools/Options/General to set the length of the displayed list. I found that it was already set to 9 entries. However, in the File pulldown, the list is not displayed. I created a dummy test XLS, and indeed it is in the list, but nothing previously created. What might be clearing this list? Don't know if applies to Office 2007 but here's one guess........ For Office 2002 and 2003............ Are you running TweakUI? If so, you may have disabled the MRU lists by unchecking "maintain document history list" under the TweakUI Expl...

Different color & trend for data at specific starting point
I track (line chart) workhours for each day of the month. The last week of the month a procedure changed. Can I have the last weeks workhours in a difeernt color than the rest of the month ? And can I trend only a specific portion of the month, such as the last 10 days of the month ? Could tect be added to the data point on the chart where the change began, e.g. 7/25/07 ? Thanks, ...

Update MS 2004 Bank List.
Hello, While using my MS 2004 I started to click on the links to MSN Money and found that it is able to connect to the banks I have accounts in. Why does my MS 2004 not have these in its list? I was given the MS 2004 in hopes to automatically download my account information. Surely I do not have to upgrade to the 2005 edition just to gain access to the banks that did not make the 2004 release?!? I am not a power user by any means and I just want to access the banks with my barely touched product. Surely you *DO* have to upgrade to the 2005 edition to gain access to those banks. They d...

Export to Formatted Text (.prn) file truncates data
When I save the excel file as PRN file (text file) the data is truncated on the right. Not all the columns are being exported. Does anyone know how to fix this problem? Thanks First, excellent choice in names and initials! You have a few choices (try against a copy of your worksheet): I'd either concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Then I'd copy and paste to notepad and save from there. Once...

Concise list of Jet functions
Can someone please direct me to a listing of Jet functions & data types? While I have a fair amount of Access programming under my belt, a current project has me providing data via queries called by an ADO.NET interface developed by another developer. Of course, I cannot use any of my custom functions, only the native Jet functions (IIf, Date(), etc.). To save some trial-and-error, I would like to obtain some Jet reference material that lists the functions available natively so that I know what I can safely embed in my queries. I found out the hard way that NZ() does not w...

Listing Accounts without any activity created on it
When a Account is assigned to another owner he/she will see this assignement as a new Account line in the Account list mixed with the existing accounts of this owner. How an Advanced Find can be done in order to create a View to show only the assigned account, especially if this Account has no any Activity against it? Hjar, you could use the workflow manager to send a new email to the new owner. HTH, Frederic - Travi@ta www.microsoft-dynamics-crm.eu hjar schreef: > When a Account is assigned to another owner he/she will see this assignement > as a new Account line in the Acc...

Exporting Excel data to Oracle DB
I am looking for a way to take information from an Excel spreadsheet and move it into an Oracle database. My first question is, can this be done? And second, what are the limitiations? I would greatly appreciate any info. Josh ...

Can Blocked Senders List size maintained by Exchange Server 2003 be increased?
I am running Exchange Server 2003 and my Outlook 2003 client has a large Blocked Sender list to filter Junk E-mail. I am getting a message saying the size of the folder managed by the server is out of space. Is there a way to increase the size of this list in my Exchange Server? When I first experienced this problem, my list had around 10,000 entries. I have exported the list a few times and run a macro to re-import the list as only unique domain names and this has allowed the size of my list to grow to around 11,390 entries, but is a slow process and I am getting this message more frequen...

How do I set up a drop down validation to ignore any blank cells?
I have a drop-down list that refers to list of information that is not always going to have values in every cell in the range/list. I need to know how to set up the drop down so that it only returns values and ignores the blank cells in the range. MLP, I am not really sure if you can do what you are asking or not, but one option might be to make another list that refers to your first list but ignores the blank cells or places them at the end of the list. Use something like the "small" or "large" worksheet function for this (see the help file for more info). Th...

Distribution Lists and null sender problem
Exchange 2003 is sending messages from our distribution lists with null senders. E.g., when it connects to the recipient’s server, it sends the following: MAIL FROM:<> instead of MAIL FROM: <jblow@nlc.state.ne.us>. Normal messages are fine—this behavior occurs only with distribution lists--and seemingly only distribution lists over a certain size. This is a problem because several ISPs (including a biggie--Yahoo) have implemented anti-spam measures that only accept a single recipient when the envelope sender is null. We learned recently that large numbers of our customer...

Public Folder access permission list
I want to get the list of persons who all are having permission on each individual public folder available in the organization... Is there any vbscript or tool available to get this kind of report for audit purpose? Greatly appreciate for any help Amit I'm sure pfdavadmin can export all the public folde permissions to a file for you... -- Susan Conkey [MVP] "Amit" <Exchange.Amins@gmail.com> wrote in message news:1177622099.084900.13270@r30g2000prh.googlegroups.com... >I want to get the list of persons who all are having permission on > each individual publ...

Outlook 2002 Sender List and email not working right
Two problems: 1) the auto complete feature is not working automatically filling in the name on the "To" or "cc" box even though I have the option checked to do so. 2) The "select names" list box that appears when I click the "TO" header of a new email has duplicate entries for each contact. One if I have the email address in the contact info and once again if the contact has a business fax entered. Anyone have suggestions? Sounds to me like normal behavior on both counts. You won't see any autocompletion until you've sent enough email...

how can deactive cells to input data #2
help me plz if you see one of the templates like balance sheet in excel some of the cells are deactived and users just input data in special cells. so how can I deactive cells that users can not input data? thanks a million ...

Money 2004: Displaying an associated cash account in the Account List
My brokerage firm provides me with an investment account that also acts as a chequing account. As a result, I track the cash balance of the account in its associated cash account. It appears that in order to get a summary of the balance, I must switch to either the portfolio or transaction view. Is it possible to view a summary of this associated cash account in the main account list? I would like to do so without creating a new cash account and transfering all the transactions to it. Thanks! Steve Guidi In microsoft.public.money, Steve Guidi wrote: >My brokerage firm provides me wi...

Changing to the Contact list as the email default address list.
I want to change the address book that automatically comes up when you use the TO field in an email message from the Global Address List to the Contacts list. Is it possible and how do I achieve. Kris <anonymous@discussions.microsoft.com> wrote: > I want to change the address book that automatically > comes up when you use the TO field in an email message > from the Global Address List to the Contacts list. Is it > possible and how do I achieve. Click Tools>Address Book. In the address book window, click Tools>Options. Change the "Show this address list fi...

Data validation
When I use column of data as source for list of allowed values it does not give me unique values (like AutoFilter does for example). I get the same value as many times as it appers in list source. The other problem is that values in a list are not sorted as they are in AutoFilter. Looking at the questions in Excel User Goups I can see many users see this as a problem... Regards, Oscar. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button...

How can I send an e-mail including data from a spreadsheet?
I have customer's data in a Excel Spreadsheet, I want to send an e-mail to each customer including the data by customer. Is there a way I can perform this work?. Thanks in advance for any help! Hi jcfv Do you have the data from each customer in a seperate sheet or do you have it in one sheet.? -- Regards Ron de Bruin http://www.rondebruin.nl "jcfv" <jcfv@discussions.microsoft.com> wrote in message news:EE9AE059-7F67-46D4-B201-55085FFADCBD@microsoft.com... >I have customer's data in a Excel Spreadsheet, I want to send an e-mail to > each customer includin...

Creating seperate sheet lists from larger list
I have a list the is in one sheet - this list contains names an departments and then the data that I seek. Currently there are seperate lists on different "department" sheet that break down the people that are on the complete list so tha department managers can click on their sheet and just view their data. This is all done manually. The problem: With turnaround (hiring, firing, dpeartment moves, etc these sheets are becoming a nightmare to maintain. The question: Is there a formula that I can use that will use the mai listing as a starting point that will filter the other sheets...