holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to tell excel that if a cell has the word 'HOL' in it then it
should extract a value from a specific cell on a separate spreadsheet?

If so can that formula be incorporated into the one below which is used for
automatically deducting breaks based on time worked.

=(H7<6)*H7+(AND(H7>=6,H7<8)*(H7-0.5))+(H7>=8)*(H7-1)
 
Something like this?

=IF(J10="HOL",(Sheet2!H7<6)*Sheet2!H7+(AND(Sheet2!H7>=6,Sheet2!H7<8)*(Sheet2
!H7-0.5))+(Sheet2!H7>=8)*(Sheet2!H7-1),"")


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
 
I read your question differently then Bob.

If H7 contains "Hol" instead of hours worked, then return the value on
SheetB in cell A1:

=IF(ISNUMBER(H7),(H7<6)*H7+(AND(H7>=6,H7<8)*(H7-0.5))+(H7>=8)*(H7-1),IF(H7="
Hol",SheetB!$A$1,"No Data"))
 
=(H7<6)*H7+(AND(H7>=6,H7<8)*(H7-0.5))+(H7>=8)*(H7-1)

This formula refers to a previous cell where the hours worked in a day are
calculated, the above formula is then used to subtract any breaks based on
the hours they have worked. The hours worked are taken from a 'rota' which is
a seperate sheet in the document (and the only one designed to be printed)
the shifts are entered into this rota and then all calculations are done on a
seperate sheet. What I would like to do is if someone is on holiday, be able
to type hol into a spare cell on the first sheet and then have the second
(calculation) sheet take a value from another seperate document (which is
used to work out their average hours) and insert that instead of the shift
with breaks figure. I would still need to keep the above formula in place to
calculate hours if the word hol is not inserted into a cell on the first page.
 
Back
Top