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?
View 2 RepliesI have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:
Choice 1
Choice 2
Choice 3
Choice 4
I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4
Is this possible and if so, how?
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
It is basically a simple sheet that lists the price (Column C) of different items (Column B) based on their Category (Column A).
I am trying to have an automated pricing sheet (on different sheet) utilizing data validation and drop down lists.
For example each row should have this, the drop down list in the first columns should list all the Categories available (This part works fine for me and i can take care of the duplicates).
Once that has been selected, i have now the option of selecting using drop down list also (in second column) the items corresponding to the Category selected in first column (Only the items that are part of that specific category should be listed in the drop down list).
Once the item has been selected, the price of that respective item should be listed automatically in the 3rd column.
I have attached 2 files to explain what I really need:
In file A.xlsx I have 2 sheets. In the B1 cell from Sheet 1 I have created a data validation criteria based on the sheet 2.
I would like to get that same data validation criteria based on the A.xlsx onto the B.xlsx.
Is it possible? If not do you have any trick, solution, workaround to link a list from one excel file to another?
I need cell H18 to look at cell F4, and if the value in cell F4 is 'CFB', I need validation to limit the entry in cell H18 to the values in a named range 'pripro' to populate. That part I know how to do. (=pripro)
But if the value in F4 is CCP, I need H18 to allow anything. I'm having trouble adding this to the first part.
I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.
For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.
I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.
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 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.
F
G
H
Area
City
Resource
[Code]....
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
Majors & SBS
SERVICEMB@Test.COM
#ACCT-San Dimas
CompHR
N/A
N/A
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 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.
View 9 Replies View RelatedI 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
I am a beginner with vba coding
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......
trying to substitute out values that i am using in data validation. From the sentence:
Consulting (Logistics, IT, Management)
I would like to remove the spaces, the (, the ) and the commas so that it reads:
ConsultingLogisticsITManagement
So that i can use it for data validation.
In the data validation box i am trying the formula:
=INDIRECT(SUBSTITUTE(substitute(substitute(substitute(c2," ",""),"(",""),")",""),",","")
But i keep getting told i have an error.
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.
about data validation drop downs.
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 a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
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
View 2 Replies View RelatedI 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:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
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)?
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
[Code].....
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.
View 9 Replies View RelatedI've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx‎
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.
View 2 Replies View RelatedSo, 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.