I'm trying to create an excel template to log tickets , which should allow me to categorize the tickets in two to three levels. For ex : In the spreadsheet attached , in I4 I have done the first categorization as Fruits , now J4 should populate the list based on I4 selection. I have attached a sample spreadsheet for better explanation.
View 2 Replies
I am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
I have a drop down validation list on sheet A that refers to my list on sheet B. I would like to formulate a cell on sheet C to give a value from a cell on sheet D based on which item is chosen from the validation list.
View 4 Replies View RelatedI have a validation list list in column E "Check, Charge, Credit Card, Other". in another column i have what i thought was a straight forward IF statement: =if(E3="Check",9999,"[ ]")
It does not recognize "Check" as the condition. Is there a tweak i can do to get it recognize the data validation.
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.
In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.
I have attached a sample spreadsheet if I've not explained very clearly.
TIMESHEET sample.xlsx
I seek to create a date validation list based on information is another cell (see images)
Based on (data validated list) Cell B4 - have a validation list drawn from another tab on the same worksheet "Sub regions"
equally the same with sub regions and citys...
I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell. http://i32.photobucket.com/albums/d3...n/untitled.jpg. would be an example....
What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.
In sheet 1, there are two columns, Category and Subgroup. The cell validation for Category is pulled from the category list on sheet 2. Basically, what I want to happen is depending on what is selected as the category, only the related sub-groups would be populated in the sub-group validation dropdown list. In other words, if Fruit is selected as the category, I only want Apple, Orange, Pear, Banana, etc to be in the validation drop down list for the sub group. If vegetable, then only Corn, Green Bean, Cucumber, and Peas should be shown.
I've included two options for the relationship list as I'm not tied to either style and not sure which would be easier to work with.
Every two weeks: During Payroll, we have a list of managers and specific employees under that manager.
The managers name is in Column A and they repeat.
Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.
This is a long tedious process as there are close to 30 managers.
1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?
2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"
What I would like to do is hide rows 27:30 if the data validation list is equal to "Select Product...". The list is in B27:B30. I've search the forum but couldn't get the codes to work...
View 9 Replies View RelatedI have created a cascading data validation list, and I would like to have an adjacent cell auto populate according to the dropdown selection.
Data Eg:
Oranges 1100
Apples 1101
Pears 1102
Grapes 1103
If the user selects "Pears" from the dropdown list, I would like the adjacent cell to populate with the numerical code for "pears", in this case 1102. I am designing a new expense report form at the office, and I am at a standstill with this problem.
I'm looking for a method, vba macro or Excel code, to preform the following task:
In the C column the user chooses one of several options from a menu, let's call these options 1 - 10 (this part is already done). Depending on the choise, different things are supposed to happen:
For 1-9, the formulas for cell A - Q on the current row should be copied to the next row (but with +1 in row number in formulas of course).
For 10, 3 blank rows should be created (well they already are blank..), on the forth row down, A3 - Q3 should be copied exactly. On the fifth row, the same formulas thing that was created for choise 1-9 should be created.
Since the real xls-file is too big already and it contains some sensitive information, I can't attach it here, but I made a small and similar xls-file, that better explain what I want to do.
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View RelatedI have a List of Different Fruits in Cells A1 to A5
Apple
Banana
Orange
Strawberry
Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.
What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.
Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
In all the cells in column F I have a validation list with several predefined choices, but at the bottom of this list I want an "Other..." option, which when chosen, enables the user to enter a new string in that cell if none of the predefined did not match.
View 7 Replies View RelatedI want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.
View 3 Replies View RelatedSee attached spreadsheet, in sheet 1, yellow columns are for restricted cells and Sheet 2 contains the lists for such columns' validation range. Sub-Strategy is dependent on the choice made from Main-Strategy, e.g. if Main-startegy = EquityHedge, then the choices for sub-strategy can only choose Long Biased, Low Beta & opportunistic. But I also have a "Other" category under Main Strategy, Sheet 2 cell G1, if this is chosen then I would like to remove the data validation in the sub-strategy column, (i.e. u can input anything for sub-strategy) how can I do this?
View 2 Replies View RelatedI have been searching through the site trying to find a solution to my issue, but I just can't seen to fixt it. Here is the scenario. I am working on a spreadsheet that will read some data, use that data to update the main chart and fill in the information in a table next to that chart. I recoreded 22 macros, total. Each macro corresponds to the especific type of chart that I need/want.
I want to be able to click on a drop down menu above the chart which will have each of the 22 options. Once the user clicks on the desired option, the specific macro will run and do that it should do. When I run the individual macro (without the VB that I wrote and will mention below), the system does what it should do. Everything looks dandy. But then I added this VB code to the sheet where the chart is:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Range("A12").Value
Case "1"
Call a
Case "2"
Call b
Case "3"
Call cc
Case "4"
Call d
Case "5"
Call e................
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.
Exampl of data:
Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".
Column K is where the Data Validation List is located with a in cell drop down.
What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.
This would be so I can keep a historical of programs and dates taught.
And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
View 2 Replies View Relatedi mtrying to get a validation list change depending on what is chosen in another list. I have attached an example, Yellow box is my validation and weather List 1 or List 2 in chosen I want the red box to be a choose of the list attached to those options. I've tried to put an If in there but I’m al a loss.
View 3 Replies View Relateddear....can i set width in list box data validation (width in cell i set short).thanks
View 9 Replies View Relatedis there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.
View 2 Replies View RelatedI am trying to make some of my data validation dropdown list boxes narrower. I was told one way to do that is to use combo boxes. (If there are any other ways, please let me know!)
I don't think my combo boxes are working. I had tried to make a combo box appear in each data validation pulldown cell by using this tutorial:
http://www.contextures.com/xlDataVal10.html
I followed this tutorial word for word, but when I try to test the code at the end by double-clicking on a data validation pulldown cell, no combo box appears.
I have a data validation cell that has 5 items to be selected, lets say A-E.
I would like a macro that checks and does the following:
A or B selected, hide row 25-34, rows 45- 53
C or D selected, hide row 24-44
E selected, hide rows 34-53
In each case, the macro should check and unhide all rows between 25-53 before doing the above
