Formula For Data Validation

Apr 20, 2009

iam looking for formula to display the condtion result .

i have spec in col B and tolerance in col C and col D ( +ve & -ve tolerance respectively) from col E to I ,observed reading data & col j for results.

i want to check the value of col E to I for B C D value n if entire 5 values are within spec then display "0" in subsequesnt col J else display "1"
OBSERVEDRESULTS.L.NOSPEC`+TOLERANCE`- TOLERANCE123451250.20.2262525.225.425.22100.20.2350.20.2450.20.25510.20.2

View 9 Replies


ADVERTISEMENT

How To Use INDEX Formula In Conjunction With Data Validation To Output Data

Jan 9, 2014

Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.

Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).

Also, the Data Validation dropdown includes blanks which I would like to exclude.

I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.

View 4 Replies View Related

Data Validation/Formula

Jul 3, 2008

I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.

Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702".

View 9 Replies View Related

Data Validation And List With Formula?

Jun 29, 2014

I am trying to make a simple planning sheet.

I have a workbook with 2 sheets.

1.sheet contains Columns : DayDateTimeClassTeacherCovered

2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.

A row therefore looks like this.

7/1
Harley

Lidia
Livia
Thais
Salete
Carla

My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.

Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.

View 11 Replies View Related

Data Validation - Formula Creation..

Feb 11, 2009

I would like the individual cells of C1 and D1 to allow me to input values up to 100. The tricky part is that if D1 equals 100 I would like C1 to be less than 100 and vice versa, if C1 equals 100 then D1 should equal less than 100.

Ex. C1 = 100 & D1 = 10

This should never happen: C1 = 100 & D1 = 100. I would also like an error box to show when either of the cell values exceed 100.

View 2 Replies View Related

Getting Formula For Variable Data Validation

Dec 18, 2012

want to get a formula where I can get the details of City if searched Brand wise and details of brand city wise. I also want option to search say five city names at once and get the brands available in all those cities. I have updated a excel with some dummy data.

View 7 Replies View Related

Edit Data Validation Formula From Vba

Jan 14, 2010

I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.

I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)

View 2 Replies View Related

Offset Formula In Data Validation

Jul 16, 2006

How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.

Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.

I have attached a copy "data validation test" to help make things more clear.

View 7 Replies View Related

Nested Data Validation: Figure A To Enforce Dual Data Validation On A Single Cell?

Aug 19, 2009

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 Related

Formula To Remove Last Letter To Use It In Data Validation

Mar 3, 2014

I'm trying to set a data validation in B1 from values in A1:A5.

A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes

What I want is show in dropdown list the words in singular (without the "s" at the end).

The formula below works if I introduce it in a normal cell,

[Code]....

or as array formula with CRTL+SHIFT+ENTER in this way

[Code] .....

But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.

View 7 Replies View Related

Data Validation With User Formula Ability?

Feb 20, 2012

if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.

View 4 Replies View Related

Data Validation Limitations- Shortened Formula

Jun 14, 2007

I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.

Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67

Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)

Method 1 ...

View 9 Replies View Related

Change Tab Name Reference In Formula W/ Data Validation

Feb 16, 2010

I have a range named as follows in a formula:

BaseCase!$O:$O

I have a data validation from which the user can choose another tab.

So, how do I change the reference above to:

TabName!$O:$O

so that TabName refers to the text string in cell B2 (data val. box)

I tried to use TEXT to no avail. I imagine there is a simple solution, but I am at a loss.

INDIRECT perhaps?

View 9 Replies View Related

Data Validation For Mixed Numeric And Text With Formula Only?

Feb 22, 2012

Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)

in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:

280838ZNOV11

is it possible to use a Custom Data Validation formula to ensure the user follows this format?

View 9 Replies View Related

Data Validation Cell Not Recognized In Array Formula

Nov 14, 2013

I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...

formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}

A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)

The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.

View 7 Replies View Related

Limit Text Length In Cell With Formula By Data Validation?

Jan 25, 2014

Can I limit the text length in a cell that contains a formula? You may say "Limit the text length in the input cell". That can't be done because the formula in the resultant cell takes text from two other cells in addition to the input cell. I need to limit the overall text length to, let say, 50 characters.

View 7 Replies View Related

Using Frequency In A Data Validation Formula To Ensure Unique Entries

Mar 11, 2014

Using frequency in a data validation formula for ensuring unique entries.

Currently I use COUNTIF($B$2:$B$244,B2)

View 4 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

Custom Data Validation Formula To Limit Cell Value To Maximum Of 4 Decimal Places

Mar 5, 2009

I require a custom data validation formula to limit cell value to maximum of 4 decimal places.

0.0001 ok
0.02 ok
0.3 ok

0.12345 fail
0.123456 fail etc

View 2 Replies View Related

Data Validation Used To Create Separate Data Validation List

Feb 15, 2014

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

First Name
Surname

Paul
Smith

Paul
Jones

Tony
Phillips

View 1 Replies View Related

Data Validation: Can I Use Data Validation In A Cell That Has Formulas In It?

Apr 3, 2009

I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.

View 2 Replies View Related

Data Validation :: Selected From 4 Validation Lists

Sep 26, 2007

I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?

for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...

(I do not want to use a combo box for this)

View 9 Replies View Related

Data Validation - Add Some More If / Else But Validation Don't Allows It?

Jun 30, 2014

I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.

=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))

i need to add some more ifelse but the validation don't allows it.

View 2 Replies View Related

Validation And Formula Combined?

Jan 13, 2010

I have a Yes/No list that I use in two cells for data validation:

Cell A1 has the Yes/No list for data validation.

Cell A10 has the Yes/No list for data validation.

I want A10 to become No if the user selects "No" in the A1 pull-down list.

View 3 Replies View Related

Validation Formula For Letters

Oct 12, 2007

I am trying to validate a Grid Location, example: 22AX21321232

How do i validate letters in the formula?

View 10 Replies View Related

Data Validation - How To Create Dropdown List That Excludes Duplicate Data

Sep 26, 2013

I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.

For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?

View 5 Replies View Related

Load Rows Data On Selection Of Data Validation List

May 28, 2014

I want to load the actual data exist in the rows by selection value from the data validation list.

E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.

On the second sheet, i made a data validation list of all the person names

Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.

View 13 Replies View Related

Formula Based On Validation List

Aug 17, 2009

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 Related

Validation Lists As A Result Of An IF Formula

Apr 23, 2007

is it possible to first make an IF check on a cell and then as one of the results (true or false) to give the cell a Validation List?

So for instance if the IF will come out true, the Cell would just diplay some text and if the IF will come out false, the user will be able to choose something from a Validation Drop-down List?

View 9 Replies View Related

Use Data Validation That Will Pull Data From A Source List

Jan 12, 2010

Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?

View 3 Replies View Related







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