EXCEL CONDITIONAL VALIDATION LIST

[dilworth]

I am trying to create a formula in the Data Validation (Excel 2002) that only allows data to be entered if data has been entered into another cell in the same row-this needs to follow the same logic for 4 columns i.e. is it possible to use Data Validation to control the order that data is enetered into a spreadsheet.

The spreadsheet contains the following columns:-

Project Number (A)
Project Manager (B)
Work Type 1 Description (C)
Work Type 1 Estimated Cost (D)
Work Type 1 Actual Cost (E)
Work Type 1 Date (F)

I want to set the Data Validation so that 'Work Type 1 Estimated Cost' can only be entered if the Project Manager Column has been entered-if a user tries to enter data into the Estimated Cost without first entering data into the Project Manager Column then the following Error Message is displayed:-

'Please Enter Project Manager'

I then want to apply the same validation to the Project Number, Actual Costs and Date in that order.

I have tried using various formulas but they don't seem to work-any help appreciated with this information.

 

[GlennUK]

Is this any use:

http://www.tek-tips.com/viewthread.cfm?qid=1536857

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[dilworth]

The validation doesn't require drop down just to prevent useres from entering data if the cell referred to in the formula is blank.

I have tried the following in the Data Validation formula and it seems that it could work but there is an error:-

=NOT(D9)=""

(Select 'Ignore blank')

However the data doesn't validate even if data is entered into D9.

Is it not just a case of entering a formula that says if D9 is blank don't validate however if D9 is blank then allow validation or am I missing the point.

 

[GlennUK]

Sorry, I misread your question ... I'll have a think about this.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[dilworth]

That would be great thanks.

 

[buitenhek]

I'm assuming you want A to be filled in before B etc.

I would do it as follows:
a1 Project #
b1 Project Mgr
etc

cell (f1) =if(a1="",1,"")
cell (g1) =if(b1="",1,"") etc for all
then make validation formula of b1:
=if(f1=1,false,true) ignore blank, and set alert to stop with "Please enter Project Number first"
validation formula for c1
=if(g1=1,false,true) etc



Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[dilworth]

I have manged to get this to work with the Data Validation as follows:-

Cell D1 =B9<>"" Ignore blank

I have copied this to the other cells and the validation is now working however if a user enters data into B1 and then D1 following the validation and then subsequently deletes the data from B1 the validation isn't applied retrospectively meaning the user can get round the process and data is effectively entered into D1 but B1 is blank-this should'nt happen regularly but it does mean there is a way round the process.

Is there additional validation that can be used to prevent this that doesn't allow users to delete data i.e. if a user enters data into B1 then D1 and then deletes data from B1 the error message is displayed advising data to be enetered into B1-please advise any suggestions

 

[dilworth]

Also is there a way to prevent users clearing the Data Validation from the cells in the spreadsheet

 

[buitenhek]

password protect the sheet

 

[AnotherHiggins]

Is there additional validation that can be used to prevent this that doesn't allow users to delete data i.e. if a user enters data into B1 then D1 and then deletes data from B1 the error message is displayed advising data to be enetered into B1-please advise any suggestions

That will require VBA (a macro).

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.