Data Validation To Ensure Like Numbers In Cell A Has Same Date In Cell B

Jan 10, 2008

I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.

For example, if:

Row 3 column A = 1320

and

Row 3 column B = 16-Dec-07

and

Row 15 column A = 1320

then:

Row 15 column B must be 16-Dec-07

View 12 Replies


ADVERTISEMENT

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 - Negative And Positive Numbers Based On Another Cell?

Mar 15, 2012

I have created a template using data validations. I have a drop down in column O, which you can choose from Credit or Charge. In column V, I want:

- If selected Credit in column O, only negative numbers allowed in column V

- If selected Charge in column O, only positive numbers allowed in column V

View 2 Replies View Related

How To Enter Date In Separate Cell By Using Data Validation

Aug 19, 2013

I have used data validation to colour rows in one of my spreadsheets at work. The column that uses the data validation is the first column and has a drop down list of 5 words. One of which is collected, which currently indicates when the card has been collected by the individual in question and no further attention is required. It colours the rest of the row green. There is also a column that is entitled 'Date Collected'. Instead of having to input this manually, is there a way to automatically enter the date to be the date that the 'Collected' value was selected?

The title is misleading, I realise there will need to be some sort of IF statement in the 'Date Collected' column. It's been a long day already.

View 6 Replies View Related

Excel 2010 :: Data Validation Won't Accept Start And End Date From Cell Values?

Mar 16, 2014

Data validation in an Excel 2010 workbook.

I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.

As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.

But I can enter 1/9/2020 and the Data Validation happily accepts that date.

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

Ensure Value Entered Into Textbox - Validation Not Working Properly

Jul 1, 2014

I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:

[Code] .....

View 14 Replies View Related

How To Validate If Data In A Cell Conforms To Data Validation Rules In The Cell

Oct 14, 2009

I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.

I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.

[url]
To keep it simple, I'm only supporting pasting a single cell at a time.

Now my problem is this:
Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.

The following post just suggests disabling paste whenever data validation is present:
[url]

But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.

View 9 Replies View Related

Ensure Cell Cannot Contain A Certain Character

Oct 18, 2007

I enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.

View 6 Replies View Related

Ensure Cell Only Accepts X Characters

Dec 14, 2007

my formula is in B1. If in A1, there is letter Y, the value in B1 should accept only 3-digit numbers.

View 2 Replies View Related

How To Ensure Formula Will Not Change If Shift Cell Down

Mar 8, 2014

I have the formula but it will always change when i shift my cell down

=MAX($G$21:G35)

When I shift my cell G21 down, it will change to below

=MAX($G$22:G35)

The problem is that I do not want the formula to change. How can I do it ?

View 5 Replies View Related

How To Ensure Cell Entry In A Range Is Always Unique

Jun 18, 2013

I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?

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

Copy Cell Value To Cell On Different Tab That Has Data Validation

Sep 11, 2013

I have a cell in Sheet1, C5 that is a formula that returns one of 2 values.

I have a macro that copies and pastes values from this sheet into Sheet2, however I cannot get the cell V3 in Sheet2 to have the value of C5 in Sheet1.

There is a data validation list of 3 choices in V3, two of which are available from Sheet1 C5, but I want the 3rd option to remain manual.

I have been playing around with it and even tried the manual recorder, but no luck

Here are some of my attempts:

Sheets("template").Cells(3, 22).Value = Sheets("New Details").Cells(5, 3).Value

or

Sheets("template").Activate
Sheets("template").Select
Range("V3").Value = Sheets("New Details").Range("C5").Value

Neither of which have the desired effect.

View 1 Replies View Related

Add A Date Time Stamp In A Cell After Data Is Entered In A Different Cell And Date

Apr 18, 2014

I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?

View 9 Replies View Related

Excel 2007 :: Ensure Cell Postcode Have 8 Characters (including Spaces)

Oct 8, 2012

Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:

EH21 6PQ - 1 Space (8 Char)
EH12 9HG - 3 Spaces (10 Char)
E1 8DF - 3 Spaces (8 Char)
LL5 1GH - 2 Spaces (8 Char)
L5 1FG - 1 Space (6 Char)

What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char

So from the above postcodes the desired results would be:

EH21 6PQ - 1 Space (8 Char) - This would be correct
EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle
E1 8DF - 3 Spaces (8 Char) - This would be correct
LL5 1GH - 2 Spaces (8 Char) - This would be correct
L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle

View 9 Replies View Related

Data Validation :: If Cell A2 > 0 Have Cell A1 =1

Aug 13, 2009

Is there a way to use data validation for the following

cell A1

If cell a2>0 have cell a1 =1

data validation will be in cell A1

View 9 Replies View Related

Ensure Valid Date Entry

Jan 9, 2008

When a user inputs a month and then a day, I want to be able to check to make sure that the day entered is possible in that given month. Is there a way to do that which is not too complicated?

View 9 Replies View Related

Two Data Validation On The Same Cell

Sep 14, 2009

can i do two data validation on the same cell
the first one to make a list form Define name "Lists"
and the other to make sure the name is not repeated

View 9 Replies View Related

Ensure TextBox Date Does Not Exist In Range

Dec 16, 2007

Trying to enter dates across first column with userform. Must not enter same date twice. Need to searching row to skip entering the date from userform textbox if date is already there. Dates are in order but not sequential. I want to search for existing date before the following

Set LastCol = Sheet2. Range("jk1").End(xlToLeft) 'enter data on sheet2
LastCol.Offset(0, 1).Value = TextBox2.Value 'date
Rows("1:1").Select
ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Add Key:=Range("b1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").sort
.SetRange Range("b1:jk10000")
. Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

View 2 Replies View Related

Data Validation Based On Another Cell Value

Aug 20, 2014

I have already set up a data validation, if if you pick a region, you only get that regions items in the next column.

A
B
C
D
E

[Code]...

But what I would like to do is in Column E(for this example) have 3 extra lists for Location in each region. So if I pick S in A, then in E I only want the Southern options.

I have checked out this website, but I cant seem to work out how to add the third dependence [URL]....

View 5 Replies View Related

Data Validation Based On Cell Value

Jul 14, 2009

I am trying to set the data validation on a cell based on another cell value

So basically if the User enters say CAN in cell A2, cell B2 should use "listCAN" range as the data validation list

and if i enter US in A2, B2 should use "listUS" as the data validation list

View 6 Replies View Related

Data Validation Contingent On Another Cell

Nov 24, 2009

How can I create a drop down list, where the items available for selection in this drop down list are contingent upon another selection made in a seperate drop down list.

View 2 Replies View Related

Use If Statement In Cell That Contains Data Validation

Sep 29, 2011

I have a cell containing data validation list and what i want to do is that when i choose the 1st value from this list .. a number (for examole 1) appears in another blank cell .. and if i choose the 2nd value from the list .. another number (let's say 2) appears in the blank cell.

So how to use the if statement in a cell that contains a data validation ?!!

View 7 Replies View Related

Check If Cell Has Data Validation With VBA

Nov 15, 2008

How can I check if a cell has data validation with a VBA procedure?

I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation.

View 9 Replies View Related

Apply Data Validation If Cell Value Is < X

Feb 2, 2009

I'm building some work life balance spreadsheets for employees to show satisfaction levels / hours etc...

For the satisfaction piece - users will enter a value between 0 and 5 with 5 = happy as larry and 0 = ready to quit. Next to this field is a comments box. This comments box only needs to be populated (from a data validation list) if the staff are unhappy.

I need the file to prevent users entering a value less than 3.99 without selecting a comment from the validation list. Users will go into the file and update for their own lines so the check can be written into the save function maybe???

View 9 Replies View Related

Data Validation - Whole Numbers

Oct 2, 2007

how can I set a cell so that you can only type in 1 , 2 , 3, 4, 5

I know I can use the validation but people can just copy a cell and paste it over.

View 14 Replies View Related

Data Validation Allow Numbers Only

May 11, 2013

The numbers will be both negative and positive just need to catch characters.

So:

2500 is okay
-15 is okay
is not okay.

View 2 Replies View Related

Highlight Numbers Of Selected Date Cell Row?

Jul 16, 2013

Example:

A
B
C
D

[Code].....

I have 49 random numbers esquire 7*7 in the cells B2:H8, and the lottery result in the cells B11:H20, and the result dates in cells A11:B20.

What I want is it possible if I select the cell for example in A11, then numbers are in cells B11:H11 can be highlighted in random number esquire, as shown in the example.

And If I select A12 then highlighted number of this row cells B12:H12 and if cell A13...A14...or...?

View 9 Replies View Related

Data Validation List Based Off Cell Value

Apr 25, 2014

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.

TIMESHEET sample.xlsx

View 9 Replies View Related







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