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.