Data Validation List Based On A Table That Can Change?
Jan 11, 2013
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 :.
If I have a table as noted below with the following assumptions:
- this table will likely grow - the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted. - Macros aren't an option as this sheet needs to be macro free.
A B C 1 Item Calories Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
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.
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.
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...
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
I found this code and am trying to use it to update the filter in my pivot table (sheet 6), based on the data validation selection in sheet 1, but when I make my selection on sheet 1, nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim pt As PivotTable Dim pi As PivotItem Dim strField As String strField = "Region"
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 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.
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 have 12 months in a year and each number represent a month. I have generated lists of data mainly interest and principle payments from 12 amortization sheets which i have just copied into the attached spreadsheet and will change accordingly based on the loan terms which doesn concern us. For our purposes we wont make distintion between principle and payment as i just need to see how to make what i need work.
What I need: I need to offset data vertically automatically once i the user selects an option value (Number in this Case) from the validation list located within the Payment control box. Going from left to right you see the raw data but in this example i need the data on the right hand side to be offset down the number of rows which may be found in the control box as a validation list.
IE:
So if i were to use "Loan I" [L6] and select a "2"[J6] from the "Payment Control" then the data on the right hand side [L6[ would reflect a $0 but instead the new value for L7 would be "$24.96" and would continue down for about 374 rows. This will have to be done for Loans I - Loans N [L6:Q6] respectively. This is all i need in order to compile and make use of in my spreadsheets.
Thanks and cannot be a formula as forumlas will be overwritten and cannot be a macro so it must be a module in itself so it an run when the list is changed.
the single digit values in coulmns M6:Q3 are there mainly so you can see the difference between the base and the position at which the data is placed at. its not need but is there to stress and to show the number rows in offset of data.
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
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
i 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.
I have the following code to update a pivot table:
Code: Dim pt As PivotTable Application.EnableCancelKey = xlDisabled For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _ , Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
I have a cell that has a validation list. When i select a value from the list, i want the value of another cell to change automatically but it isnt working. The list source is pointing to another sheet:
=INDIRECT("DB_DAT!$J$268:$J$275")
I get an error when it tries to change the cell value. Its error no. 1004.
So I've got a drop down list in cell B73 That when I change the selection I want it to copy the cells below it (B74:B94) from one of the charts above it. Through the power of Google I found this: http://www.eggheadcafe.com/conversat...eadid=29484871, someone who had the same need as me, and edited it accordingly to my needs. It however, is not working. I started with Case 1-7 and changed them thinking they need to be the same as the list but that hasn't fixed it either. File is attached, and below quote is what the VBA coding currently says.
I have 13 sheets in my workbook (one for each month plus a GlobalSettings). In each month sheet I want to create a change event that prompts a UserForm when they select "Yes" from a drop-down validation list if it happens to be a month prior to the current month.
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 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
I have a sheet which autofilters according to a validation list in cell E2 using the code.
I have then used subtotal arrays to calculate the mean, median, max, min and total count for whichever values the filter shows.
What I want to do next is have a code which will select each possible option from the validation list (triggering the autofilter) and copy and paste the values from each dependent formula into a new sheet.
I'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'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?
