Data Validation/Conditional Formatting Assistance Needed

[Freidamae]

Hello,

This site has helped me numerous times before I registered as a user. I have been digging for the right answer to my question and I can't seem to find it this time. So, here goes:

I have a time sheet spreadsheet. Hours worked are recorded in time. Hours charged to each division are calculated in numbers. Hidden, we have calculations that change the hours worked in time to a number.

I need to make it to that the sum of charged hours equals the number of hours worked each day.

In the farthest right cell -1.50, there should be a Data Validation Stop Error Alert as the -1.50 is less than 0. This is one of the many formulas that I have tried.

I can use Conditional Formatting to turn cells red (but that tells my user nothing) and I have a formula where it will return text if the cell is not 0. However, my boss would like a more blatant message. Text can be ignored, Data Validation requires a fix to the problem.

Any suggestions are greatly appreciated.

Thanks!

 

[awoohaw]

Your formula needs an "equals" sign. But, I have if Z8<0, but if you are looking for any difference, then the formula should be

=(z8<>0)

Book1
	Y	Z	AA	AB
5
6
7
8		-1.5
9
10
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z8	Expression	=Z8<0	text	NO

NOTE: this will be red while the users are entering their data. You could put an if in there as well:
=IF(Z8="",FALSE,Z8<>0))

 

[Freidamae]

I can get the red cell, but I cannot get a Data Validation Error to pop up when I try the equals sign??

 

[awoohaw]

OKay, I was thinking the conditional formatting. I'll noodle some more.

 

[awoohaw]

What cells do you have the data validation in? and what are the critical cells Columns() used in that calculation? Just Time Start, Time End, and what else?

subsequent edit: What column is the last entered that needs to match the End-Start times? is that entered by the user as well?

 

[awoohaw]

how about:

Book1
	W	X	Y	Z	AA
7	StartTime	EndTime	Hours worked Calc to Number	Hours Work (Nbr)
8	02/15/2023 08:00	02/15/2023 09:30	1.5	1.5
9
10
Sheet3

Cell Formulas
Range	Formula
Y8	Y8	=(X8-W8)*24

Cells with Data Validation
Cell	Allow	Criteria
Z8	Custom	=(Z8=Y8)

 

[awoohaw]

 

[awoohaw]

 

[Freidamae]

Your fix works for 1 cell but not the range between Columns H-N. Or it works if I put it in cell Z but no one will be entering a number into cell Z as it is a formula??

 

[awoohaw]

Your fix works for 1 cell but not the range between Columns H-N. Or it works if I put it in cell Z but no one will be entering a number into cell Z as it is a formula??

I don't know what cells you need the validation for. I asked earlier what the data entry cells were. I gave you what I thought you needed for Column Z, in relation to comparing the calculated time (End-Start) and a decimal number. Any other validation that you need would require more requirements and clearer samples and examples. But, the concept is the same regardless of what you want. Just try to mimic what I've given into the other areas of the workbook.

The xl2bb add in is great for sharing portioins of worksheets.. i suggest you get it to help people better help you.