Restrict Data Entry

P

paulstan

Board Regular
Joined
Mar 12, 2011
Messages
85
I'm trying to restrict data entry to cells that don't make logical sense.

I have highlighted 2 rows where my problem lies on the diagram below. If someone has worked zero hours (Col A), then they cannot have done any work and I need to restrict the entries in Cols B,C,D to zero, if zero is showing in Col A. Again, if Col A is zero they cannot have achieved any other tasks (Col E), so this also needs to be restricted to zero if Col A is zero. Cols A and E are set to Time and Cols B,C and D are numeric values. If possible, if Col A is zero then I need an error message to appear if anyone tries to enter data in Cols B,C,D and E that are greater than zero.

Could someone point me in the right direction and Ill try and take it from there.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Hrs</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">other tasks</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1:50</TD><TD style="BORDER-LEFT: black 1px solid">Monday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5:25</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1:00</TD><TD style="BORDER-LEFT: black 1px solid">Tuesday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7:30</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2:25</TD><TD style="BORDER-LEFT: black 1px solid">Wednesday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7:30</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">25</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1:15</TD><TD style="BORDER-LEFT: black 1px solid">Thursday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2:25</TD><TD style="BORDER-LEFT: black 1px solid">Friday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">hrs worked in each day</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 1 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 2 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 3 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">time spent on other tasks</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR></TBODY></TABLE>



Regards as always

Paul S
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Re: Resgrict Data Entry

Can't you make a conditional formatting to highlight the rows that don't make logical sense?

or must you literally not allow the user to enter 0 into column A?
 
Upvote 0
paulstan said:
I'm trying to restrict data entry to cells that don't make logical sense.

I have highlighted 2 rows where my problem lies on the diagram below. If someone has worked zero hours (Col A), then they cannot have done any work and I need to restrict the entries in Cols B,C,D to zero, if zero is showing in Col A. Again, if Col A is zero they cannot have achieved any other tasks (Col E), so this also needs to be restricted to zero if Col A is zero. Cols A and E are set to Time and Cols B,C and D are numeric values. If possible, if Col A is zero then I need an error message to appear if anyone tries to enter data in Cols B,C,D and E that are greater than zero.

Could someone point me in the right direction and Ill try and take it from there.

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Hrs</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Job 1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Job 2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Job 3</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">other tasks</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">0:00</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">1:50</TD><TD style="BORDER-LEFT: black 1px solid">Monday</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">5:25</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1:00</TD><TD style="BORDER-LEFT: black 1px solid">Tuesday</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">7:30</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">15</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">2:25</TD><TD style="BORDER-LEFT: black 1px solid">Wednesday</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">7:30</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">25</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1:15</TD><TD style="BORDER-LEFT: black 1px solid">Thursday</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">0:00</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">2:25</TD><TD style="BORDER-LEFT: black 1px solid">Friday</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid">hrs worked in each day</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid">Number of Job 1 completed</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid">Number of Job 2 completed</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid">Number of Job 3 completed</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-LEFT: black 1px solid">time spent on other tasks</TD><TD style="BORDER-LEFT: black 1px solid; TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>



Regards as always

Paul S
Click to expand...
You can do this with data validation.

Select the range B5:E9
Goto the Data tab
Data Validation
Allow: Custom
Formula: =N($A5)>0

You can enter a custom error message if you'd like. Click the Error Alert tab and enter your message.
 
Upvote 0
Biff

Many thanks for your information which seems to work fine.

One point to note is that I need Cols A and E to be restricted to just Time entries and also Col E to only accept a time less than or equal to whatever may be input in Col A. Can I combine a Time data entry with the custom one you suggested?

Regards

Paul S
 
Upvote 0
paulstan said:
Biff

Many thanks for your information which seems to work fine.

One point to note is that I need Cols A and E to be restricted to just Time entries and also Col E to only accept a time less than or equal to whatever may be input in Col A. Can I combine a Time data entry with the custom one you suggested?

Regards

Paul S
Click to expand...
Ok, what is the max number of hours that can be entered in column A?
 
Upvote 0
T. Valko said:
Ok, try these...
  • For the range A5:A9
  • =AND(A5>0,A5<=0.5)
  • For the range B5:D9
  • =N($A5)
  • For the range E5:E9
  • =AND(N(A5),E5>0,E5<=A5)
Click to expand...

Biff

That is almost there. I'm having a problem if 0:00 is entered in Col A and I try and enter 0:00 in Col E (same row) it comes up with the error message and won't let me enter data. I may need to enter 0:00 if someone does not work on a particular day, ie part-timers.



I have adapted your code slightly to read:
  • For the range A5:A9
  • =AND(A5>=0,A5<=0.5)
  • For the range B5:D9
  • =N($A5)
  • For the range E5:E9
  • =AND(N(A5),E5>0,E5<=A5)
Regards

Paul S
 
Upvote 0
paulstan said:
Biff

That is almost there. I'm having a problem if 0:00 is entered in Col A and I try and enter 0:00 in Col E (same row) it comes up with the error message and won't let me enter data. I may need to enter 0:00 if someone does not work on a particular day, ie part-timers.




I have adapted your code slightly to read:
  • For the range A5:A9
  • =AND(A5>=0,A5<=0.5)
  • For the range B5:D9
  • =N($A5)
  • For the range E5:E9
  • =AND(N(A5),E5>0,E5<=A5)
Regards

Paul S
Click to expand...
Well, in previous posts you said you needed the time to be >0.
  • For the range A5:A9
  • =AND(COUNT(A5),A5>=0,A5<=0.5)
  • For the range B5:D9
  • =COUNT($A1)
  • For the range E5:E9
  • =AND(COUNT(A5),E5>=0,E5<=A5)
 
Upvote 0
Biff

That works like a dream.

Apologies for the slight error on my part - I was confusing myself (not that difficult to do).

Many thanks for all your wonderful help and assistance.

Signing off in this part of England!!

Regards

Paul S
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
V
  • Solved
How to style table border ?
Replies
4
Views
513
excel_learnerz
E

Forum statistics

Threads
1,224,564
Messages
6,179,547
Members
452,925
Latest member
duyvmex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top