Data Validation All At Once Across Multiple Worksheets
Jan 2, 2007
I have 37 workbooks that are timesheets for employees for calendar 2007. Each one has 52 tabs for the weeks of the year. Each worksheet has a particular column that needs to have a drop-down selection. That's working fine as long as I do it one worksheet at a time.
Since I don't relish doing this 1,924 times (37 employees x 52 weeks), I opened a workbook and selected all tabs (first, shift, last), but when I tried to highlight the cells that need the drop-down info, "Validation" under "Data" was grayed out. I checked another sheet and found the same thing. As long as I do it one worksheet at a time, it works fine.
Can data validation, for the purpose of adding drop-downs, not be done across multiple worksheets within the same workbook?
I have researched and used some code that allows the user to select mutliple items from a list. This is using the Data Validation tool with a list and some code in the relavent sheet. It all works well and as described.
However, when I lock the Worksheet, the multi selection no longer works. Selection cells are ofcourse unlocked when sheet is protected.
I have also trialed unlocking the whole row that the multi selection exists in, no effect.
My understanding of this code is at about 50%.
Is there a way of being able to lock the sheet and retain this ability of multi selection. Maybe there is some other code I could refer too?
Code: ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range
Sheet A - The user input form. There is a column which needs data validation. I want it to look at Sheet B and ONLY accept values based on a column value from the other sheet
Sheet B- Contains the list we want the data validation from. One column is considered the lookup value, while the other is what we want the user to be selecting from.
Here is an example list:
Predecessor Entity Key---- Item ID 2659407------------------- SHELL - SOUTH TOWER 2659407------------------- MARKING / LABELING 2659407------------------- FOUNDATIONS & SUPPORTS 2659410------------------- UPPER HEAD - NORTH TOWER 2659410------------------- FOUNDATIONS & SUPPORTS 2659410------------------- LOWER HEAD - SOUTH TOWER
So, in Sheet 1, if one of the triggering values is 2659407, I want my data validation to ONLY Allow the first 3 values from the Item ID column.
The issue I am having is using a pretty messy formula to try and produce a dynamic range for the Data Validation to use.
Here is what works:
=INDIRECT(CONCATENATE(ADDRESS(MATCH(J4,A:A,0),2),":",ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(MATCH(J4,A:A,0),2)),COUNTIF(A:A,J4)-1,0)),2))) where J4 is the lookup value/1st column depicted in our simple example
This is done on the SAME sheet. So I figure, just add "'Sheet Name'!" for the first argument in CONCATENATE. Unfortunately, this does not work. How to get this to work on an outside sheet?
The error I get is that I cannot Reference other worksheets. However, If I type in ='Profile Item Library'!B1:B18 for the Data Validation argument, it works.
I have a spreadsheet setup like a timesheet: Employee Name, Classification, Rate, etc.
I setup the Employee Name field as a drop-down. Since there are numerous employees with different rates, I want to just select a name so that the other fields fill-in with the appropriate rates.
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
I would like to put a data validation check on a cell to make sure only numbers are inputted in the cell (As opposed to words). So I was looking to write in 2 data validations: 1 to say =value(A1) and the other to allow a zero to be inputted as well. How can I write these both in?
I am trying to create a Data Validation drop down, that is based on contents of 2 different cells, without using VB. I tried doing this with an "if" formula, but it did not work.
Column F is a DV with a list for Area. I can use "=INDIRECT(F115)" to have separate ranges for column G, but I would like to use DV in column H, based on the values in F & G.
Is there a way that I can set up more than 1 sublists in excel? Below is the criteria and conditions. I was able to create the Primary List and then the first sub-list but that is it.
Primary List:
Business Unit 1. Majors 2. SBS
Sub Lists 1: Case Type/Request (All Case Types should link to the primary lists for all business units)
Sub List 2:
Depending on the case selection in sub list 1, this should show the Queue Name and/or Email Address that the case should be routed too.
Below is a screen print to show the setup of the excel file.
Column A is the Business Units Row 1 is the Case Type/Request Column B/Row 2 and on are the actual queue names and/or email addresses.
Business Unit Manual Billing File(s) uploads San Dimas Billing Inquiries
I am needing to create 2 drop downs that are dependent on 1 drop down. I have named lists that are on another worksheet. I've gotten so far as getting the 1st 2 drop down lists work but my third drop down I just can't figure out what the formula needs to be. HELP!! I've been working on this for a week now and I'm losing my mind. I've checked the contextures website and it does NOT answer this question.
I have multiple sheets (Sheets "A", "B", "C", etc) with the same structure and formatting. For these sheets ("A", "B", "C", etc.), I want to create dropdown lists in column F (cells F2:F100) based on values from a different worksheet (Sheet "DropDown", Cells "B2:B130").
I saw a previous post [URL]... which had a single sheet example. I am trying to come up with vba code for multiple sheets and so far it's not working.
VB:
Dim wkst As Worksheet For Each wkst In ThisWorkbook.Sheets ThisWorkbook.Names.Add Name:="listdata", RefersTo:= _ "=dropdown!$B$2:$B$130" With wkst.Range("F2:F100").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=listdata" End With Next End Sub
Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell? For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.
I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......
I have multiple worksheets with different data which are as follows:
Sheet 1 - Pricing Sheet 2 - Selections Sheet 3 - Summaries (created later with a pivot table)
For the most part I have the sheet working as I want it to, the issue I have is on the selections sheet. The Selections sheet has data validation linked to the All Data sheet via a drop down list, what I would like it to be able to do is to display in the dropdown list 2 separate columns from the All Data list rather than just the primary list.
Allow me to elaborate:
Sheet 1: Column A; Socket, Socket, Socket, Cable, Cable Column B, Supplier A, Supplier B, Supplier C, Supplier A, Supplier B
So just to confirm I would like to be able to select the item from column A but it also display (while selecting), column B side by side so that a comparison can be made as part of the selection process.
I've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.
I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.
I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.
Column A Column B Column C 1 Name 1 Skill 1 Phone # 2 3 4
This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)
Column D Column E Column F 1 Name 1 7 555-1111 2 Name 2 5 555-2222 3 Name 3 3 555-3333 4 Name 4 4 555-4444
So when I select say name 3...
Column A Column B Column C 1 Name 3 3 555-3333 2 3 4
I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx
I am trying to apply data validation to a column of cells using named ranges. However, each row has a unique associated named range. For example:
A_______B Birds____* Dogs____* Cats____*
I can easily apply data validation to these three rows separately using named ranges. Ie three separate named ranges: =Birds =Dogs =Cats
However, I need a way to quickly apply data validation to column B using different named ranges for each row because there are about 2,000 rows. Is there a way to reference text in the cells of column A that contains the name of the named range? Or maybe a bit of VB code that could do it quickly?
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted? Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed? ... “SATURDAY (ARRIVE)” = “USD 100.00” ... “TUESDAY (DEPART)” = “USD 85.00” ... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula: IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
Can you create a Pull-down List that contains data from Multiple Ranges.
If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?
I am trying to make a drop down list and based on what I select from the drop down needs to populate a different drop down list with information based on what I select.
For example: if I have Sheet1, Sheet2, Sheet3 and Sheet4. Inside of Sheet4 (My Main Sheet) I have a Data Validation (Drop Down list) in Cell A5 and Cell B5
I would like to choose Sheet1 from Cell A5 and in that same sheet (Sheet4) in Cell B5 I want to have a drop down with a list of names that are located in Sheet1 in Column A:A. Or If I would've picked Sheet2 in Cell A5 of (Sheet4), I would like to see a drop down with a list of names in Cell B5 (Sheet4) that are on Sheet2 in Column A:A . Is this possible?
So far I could achive it if information I want is located on the same Sheet. But I need it to work if info is located on different Sheets
is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
So, I need to figure out how to make the populated contents of a single dropdown box dependent upon the selected results in two other drop downs.
As there are ~35K lines in the sheet, creating Lists is impractical, and VBA is out due to client security settings.
My data sort order is: Region Name > Area # > Facility Name
I have a drop down to select the Region I want to work in.
The drop-down for Area # populates based on the selection from Region Name.
The hang-up is that the area numbers are 1-4 for each Region.
So, I can't simply populate another drop-down for Facility from the result from Area #, but it has to be based upon the results of both of the former results.
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 have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
I've found a nice looking formula for looking up multiple values from a Data Validation List which you can find here.
Unfortunately I dont know why im getting a #REF! error for one of my spreadsheets. Here is my Formula {=SUM(VLOOKUP(A2,D:D,{6,7,8},0))}
A2 is the Data Validation drop down list that has most of the Values (Letters & Numbers) I want to look up. D:D is where it will find the Values (Letters & Numbers) and {6,7,8} is the currency I want added up and displayed.
I've tried this formula on other spreadsheets with success, but no success with the spreadsheet im working on currently.
I watched a video on youtbe. And the women had three or four items in drop down box. She was using vba. When i emailed her to ask her how she did this, she kept telling me to go to her website and i was supose to figure it out on my own. I am trying to do a start up construction company. the link to her site is as follows Select Multiple Items from Excel Data Validation List - YouTube the idea of what i want to do, is how she clicks on a word and it follows in the coloumn to the right going down. This is what i need to know how to do. It is probably pretty elementary to many of you. But i build, i know how to read prints, to cut footprints for new homes etc. But when it comes to excel i am so green.
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D Alabama ABC Company Active Expires December 2009 Alabama 123 Company Expired Expired April 2008 Alabama XYZ Company Active Expires August 2009 Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
