Jump to content



Photo

Data Validation in Excel

Answered Go to the full post

  • Please log in to reply
2 replies to this topic

#1 D. S.

D. S.

    Hate is the new Love

  • Tech Issues Solved: 1
  • Joined: 15-June 04
  • Location: Portugal
  • OS: Windows 7 Ultimate x64
  • Phone: Nokia 113

Posted 07 January 2014 - 19:05

Hello. I'm looking for some help with a situation I'm having in an Excel spreadsheet. I've used Data Validation before with success, but I can't seem to nail down the required formula for what I want.

 

Here's what I'm trying to do:

 

We have an Excel file that serves as a work log. Somebody does a certain task, they log it there in a 3 column system (A, B and C).

 

Column A describes the type of task and it is data validated against a List. You can only choose from 3 types of task: Documents, Faxes, and Phones.

 

The B column contains a small summary of the work done, no need for validation there.

 

The C column is where things get tricky. One of the mentioned tasks, Documents, requires that you also log the number of documents processed. I want to make it so that column can only be filled if the column A value corresponds to the correct task type and only a numerical value can be input (no text). Anything else, it should show an error.

 

I've tried using Data Validation with both AND and IF clauses but I just can't get it to work. Is what I'm asking doable? :s



Best Answer Jutx , 07 January 2014 - 19:22

=AND(A1="Documents",ISNUMBER(C1)=TRUE) should be the data validation formula in Excel 2010 to do what you want. You may want to add further conditions such as C1 > 0.

Go to the full post



#2 Jutx

Jutx

    Neowinian

  • Tech Issues Solved: 1
  • Joined: 24-November 04

Posted 07 January 2014 - 19:22   Best Answer

=AND(A1="Documents",ISNUMBER(C1)=TRUE) should be the data validation formula in Excel 2010 to do what you want. You may want to add further conditions such as C1 > 0.



#3 OP D. S.

D. S.

    Hate is the new Love

  • Tech Issues Solved: 1
  • Joined: 15-June 04
  • Location: Portugal
  • OS: Windows 7 Ultimate x64
  • Phone: Nokia 113

Posted 07 January 2014 - 19:41

=AND(A1="Documents",ISNUMBER(C1)=TRUE) should be the data validation formula in Excel 2010 to do what you want. You may want to add further conditions such as C1 > 0.

 

That works :D . I can now see what I did wrong, shouldn't have used an IF clause to start it. Really need to brush up on my Excel.





Click here to login or here to register to remove this ad, it's free!