=(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.

"Ragdyer" wrote:

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"))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
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)

"bamboozled" wrote in message
...
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)