Formatting (or Formulas)

Posted by jo on December 26, 2001 1:41 PM

Still learning the basics. Here's the question. I have a spreadsheet with about 5 sheets. On sheet 2 I have the daily hours of employees. On sheet 3 I have the daily lates & No shows for employess. On sheet 4 I have the week totals. It looks something like this.

Emp # Name Times Late Times NS Under 40hrs Under 30hrs

I want to know if there's a formula that will automatically enter this info from sheet 2 & 3 onto sheet 4. Say if they worked 33 hours it would automatically go under the under 40 column, but if they worked 20 it would go under the under 30. Also, if they worked over 40 it would come back with a blank cell. The same with the lates & ns. Please can someone help me?

Posted by Tom Dickinson on December 26, 2001 9:17 PM

I am making sum assumptions from what you have written: Sheet 2 is the hours actually worked for the week. The names appear in the same order on each sheet. Sheet 3 is 2 columns, Column B has the number of lates for the week, Column C has the number of ns for the week (for each employee). Data starts on row 2 as row 1 is headings. So, formulas would be (using row2 as the example):

column A
=Sheet2!A2
Column B
=Sheet3!B2
Column C
=Sheet3!C2
Column D
=if(sum(Sheet3!B2:B6) &LT; 40, sum(Sheet3!B2:B6), "")
Column E
=if(sum(Sheet3!B2:B6) &LT; 30, sum(Sheet3!B2:B6), "")