Data Validation Multiple Values - Without VB

May 29, 2013

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]....

View 5 Replies


ADVERTISEMENT

Substituting Multiple Values For Data Validation?

Jan 3, 2013

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.

View 6 Replies View Related

Excel VLOOKUP Multiple Values From Data Validation List

Dec 12, 2011

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.

View 3 Replies View Related

Lookup Data From Validation Drop Down And Return Multiple Corresponding Values

Jul 17, 2009

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.

View 9 Replies View Related

Data Validation Indirect Where Multiple Values Show Specific List?

Feb 7, 2014

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.

View 4 Replies View Related

Multiple Data Validation Across Multiple Fields?

Jul 23, 2012

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.

View 1 Replies View Related

Set Data Validation Default Values?

May 28, 2014

I've looked through the forum, and have seen several requests for data validation defaults, but not related to cells that need to repopulate to defaults based on an action. I'm attaching my worksheet to get an understanding of what I'm trying to accomplish.

Default Data Validation Help.xlsx

Basically, any time a new employee name is selected, I want default review begin and end dates populated to the data validation cells so that the dates don't have to be manually selected every time a new employee is reviewed.

View 2 Replies View Related

Data Validation When Introducing Values

Aug 11, 2009

- I type a number in a cell
- If the number is not a multiple of an adjacent cell, it will automatically do a ceiling to that number
- If it is a multiple it is ok.

View 13 Replies View Related

Data Validation - How To Get Only Unique Values

May 27, 2013

I have defined name in excel like:

Named: My_CARS
Refers to = My_Cars!$C$2:$C$100

Now in sheet2, I I'm doing in field A2 data validation and set up to Validatation Criteria as:
- Allow = List
- Source: = My_CARS.

But I have all values - with duplicates. How can I get only unique values?

View 3 Replies View Related

Data Validation For Repetitive Values

Feb 5, 2007

in data validation i have taken a list & assigned it to defined named range say industry ( 50 - 100 industries which are repetitive in nature)

now the problem is the data is neither sorted nor unique

but i want unique sorted data (i can not make changes in original data because of its other various linkages nested formulas)

View 3 Replies View Related

Data Validation List Depending On Other Values In Row?

Jun 25, 2014

Example:

Sheet: Users
Column A has "Yes and No"
Column B has "Names"

ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith

Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.

Peter Johnson
John Smith

No need to show yes or no.

View 11 Replies View Related

Conditional Data Validation: Show All Values

Jan 6, 2009

I need someone to go through them assigning a building number, level and location to each of them. I am using drop down menus, the first choice is building and has the following options:

B1
B2
B3
B4A
B4B
B5
B7

Depending on the option selected there maybe different locations, i.e. B1 has the following available locations:

1-4
4-16
MER
ALL
KIT

Whereas B7 only has: ALL. The third column, level, operates in a similar way.

View 3 Replies View Related

Why Values Not In Data Validation List Being Accepted

Dec 14, 2011

I have several data validation lists that are driven by named ranges. These ranges include blank cells in order to allow for expansion of the list easily - without updating the named range itself.

Seems like because of these blanks rows...excel is accepting any value. The list is visible and can be used, but the user can also enter in a value not on the list. (when I remove the blank rows from the range, the validation works properly)

View 2 Replies View Related

Data Validation :: Display A List Of Values

Jun 4, 2006

data validation to display a list of values. The problem am facing is .. this list has a lot of names & it becomes difficult to scroll through when i need to search for a particular name.. Is it possible that when I press a key on my keyboard, the name starting with that letter is highlighted in the drop down list.. This is not happening automatically using data validation... Is there a work aorund for this?

View 2 Replies View Related

Data Validation Which Allows To Select Values From A List

Jun 8, 2006

I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.

View 3 Replies View Related

Multiple Criteria In Data Validation?

Nov 12, 2012

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 Replies View Related

Multiple Sublists Using Data Validation?

Feb 8, 2014

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

View 9 Replies View Related

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?

View 9 Replies View Related

Multiple Dependent Data Validation

Oct 4, 2008

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 Related

Data Validation And Multiply Values Based On Choice

Mar 11, 2014

I've got a spreadsheet and I've used data validation to look at a list on another tab.

I need to be able to multiply values out based on what i choose on the drop down menu.

I've attached a sample spreadsheet to explain : tasks.xlsx‎

View 3 Replies View Related

Data Validation List With Static And Dynamic Values

Apr 24, 2014

Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?

View 2 Replies View Related

Data Validation - Creating Dropdown Menu With Values

Feb 2, 2013

I have a list of values (say a, b, c, d, e) and creating a drop down menu with these values in say A1. Say I chose 'c'. Now in cell A2 I want to be able to choose from the same list without being able to choose 'c' again. In cell A3 I want to be able to choose from the same list without being able to choose the values lalready chosen in A1 and A2. and so on.

View 2 Replies View Related

Stop Pasting Values In Data Validation Cells

Jul 10, 2014

I have a data validation list in col A.

I don't want ppl to be able to paste values in cell - them must either type the data or select from list.

Also - the sheet is protected but col a is open

have tried...without success

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

View 9 Replies View Related

Adding Data Validation On Multiple Sheets Using Vba

Nov 18, 2013

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

I am a beginner with vba coding

View 2 Replies View Related

Multiple Data Validation Entries Into One Cell

Jun 2, 2014

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......

View 14 Replies View Related

Data Validation With Multiple Info Displayed

Feb 26, 2014

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.

View 5 Replies View Related

Multiple Dependent Data Validation Lists

Sep 12, 2007

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.

View 9 Replies View Related

Data Validation Multiple Columns Data

Apr 17, 2007

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

View 3 Replies View Related

Limit Data Validation Values To A 1 Point Margin In 3 Different Cells

Nov 17, 2009

I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)

Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.

Summary: Display an error message if any of the points are more than 1.0 point from another.

View 7 Replies View Related

Data Validation Formula For Set Of Numerical And Text Based Values

Apr 30, 2014

I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:

I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)

And I would need the data validation tool not to include blank cells.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved