Formula- to create drop down down list based on diff criteria

Hi,

I needa formula where if i select a certain department all the people
in that department will appear in a drop down list in the template. i
am wondering if we could use IF function to do this....

to give more perspective i am trying to build a travel template which
can be used by all departments in the company to plan their trips but
instead of creating multiple tabs for each department i am wondering
if i could simply create one template and when anyone selects their
department it would automatically put a list of the people (similar to
the way a pivot works but cannot have a pivot as this needs to be a
column in the template.

this would be for the department heads so would make sense if they
could simply chose their department and all the list of their
employees would appear as a drop down.

Any idea?

Thanks!

Rosana
0
Rosana
4/27/2010 6:41:24 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
676 Views

Similar Articles

[PageSpeed] 53

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

-- 

HTH

Bob

"Rosana" <roze81@gmail.com> wrote in message 
news:bab25f22-ef9f-4f8e-a35f-6e4b3e28256f@g11g2000yqe.googlegroups.com...
> Hi,
>
> I needa formula where if i select a certain department all the people
> in that department will appear in a drop down list in the template. i
> am wondering if we could use IF function to do this....
>
> to give more perspective i am trying to build a travel template which
> can be used by all departments in the company to plan their trips but
> instead of creating multiple tabs for each department i am wondering
> if i could simply create one template and when anyone selects their
> department it would automatically put a list of the people (similar to
> the way a pivot works but cannot have a pivot as this needs to be a
> column in the template.
>
> this would be for the department heads so would make sense if they
> could simply chose their department and all the list of their
> employees would appear as a drop down.
>
> Any idea?
>
> Thanks!
>
> Rosana 


0
Bob
4/27/2010 8:06:44 AM
I think your appoach depends on the way your data ist stored
somewhere. If you can get the names of the employees and the
department they belong to from a database, you could use a
SQL query with the department as parameter to retrieve only
the names belonging to the selected department. Otherwise your
code would have to select the appropriate names. Again the
details how to do this depends on the format of the data and where
you get it from.

Helmut.


"Rosana" <roze81@gmail.com> schrieb im Newsbeitrag 
news:bab25f22-ef9f-4f8e-a35f-6e4b3e28256f@g11g2000yqe.googlegroups.com...
> Hi,
>
> I needa formula where if i select a certain department all the people
> in that department will appear in a drop down list in the template. i
> am wondering if we could use IF function to do this....
>
> to give more perspective i am trying to build a travel template which
> can be used by all departments in the company to plan their trips but
> instead of creating multiple tabs for each department i am wondering
> if i could simply create one template and when anyone selects their
> department it would automatically put a list of the people (similar to
> the way a pivot works but cannot have a pivot as this needs to be a
> column in the template.
>
> this would be for the department heads so would make sense if they
> could simply chose their department and all the list of their
> employees would appear as a drop down.
>
> Any idea?
>
> Thanks!
>
> Rosana


0
Helmut
4/27/2010 8:11:43 AM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Drop-down list #5
I created a drop-down list in a separate sheet in my workbook. I named it Vehicles. Now I want to add to the list, but I can't figure out how to do it. I know ig must be so easy, but I'm stumped. Please help. If it's a one-time occurrence, you can press Ctrl+F3, click on the name, and extend the formula. Otherwise, I would make the range dynamic. You can learn how to do that here: http://www.contextures.com/xlNames01.html#Dynamic HTH Jason Atlanta, GA >-----Original Message----- >I created a drop-down list in a separate sheet in my workbook. I named it >Vehicles...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Need formula to calculate bi-monthly pay dates
I need to create a list or table that returns all pay dates for the 15th and last day of the month for an entire year. The date must adjust forward when the normal pay date falls on a weekend or holiday, e.g. the 15th is Sunday so pay day is the 12th. My attempts have resulted in excessive formulas and no concrete answer. Do you need to account for holidays? This formula works but I don't account for holidays although it'd be easy enough to do so. I'm thinking some "date wizard" (a person that is good at date formulas, that's not me!) can probably ...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Global distribution List Not Appearing in Outlook Global Selections list
I have created a global list in AD in Server 2003 ( using Exchange 2003) and assigned my users. For some reason the list is not showing in the Outlook client global address list. I have compared this list to others i have created in the past and cannot spot any differences. What am I missing? 1. Have you got it hidden from the GAL? 2. Using cached Exchange mode? Therefore requiring a rebuild of the OAB to see it? Oliver On Fri, 16 Feb 2007 13:24:05 -0500, "rlm" <rmorton@execpc.com> wrote: >I have created a global list in AD in Server 2003 ( using Exchange 2003...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

how to automatically update inventory list with sales
Please provide the help on how to update the inventory list when some items are sold. Do you want to check this out while you wait for an answer? http://office.microsoft.com/en-us/FX011429711033.aspx Epinn "lalani" <lalani@discussions.microsoft.com> wrote in message news:07A10BC7-97FB-4E93-A686-CAAE3AA2DE88@microsoft.com... > Please provide the help on how to update the inventory list when some items are > sold. The page that Epinn points out is probably as good as any. Excel is a rather poor tool for trying to track inventory. You'll notice that the actual...

Refreshing list boxes
Hi, I have created a database so that mulpile users can add detailss to the table - tblDetails from the form frmDetails or amend details in form frmAmend. I have a list box on the main page frmMain which has a list box lstSearch which shows all the records in the database. When users add new record to the database other users cannot see the added records unless the move to another section in the datbase and the return to the main page thus refreshing the list box. Can anyone tell me if there is a way I can put a "Refresh" button on the main page that updated the list box if the use...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Textbox fomatting value based on another textbox
I have two text boxes on a form. One is a value that can be changed by the user. The second is the value 1 - textbox1. I need everthing to be in %. For example, in textbox1 the user could type 75 and it would automatically be recognized as 75% and textbox2's value would calculate to be 25%. Everytime I try textbox1's value is = to say 7500%. Any help is appreciated. Cheers, Job Maybe something like: Option Explicit Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim TB1Val As Double With Me.TextBox1 If IsNumeric(.Value) Then TB1Va...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...