holidays

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)

"bamboozled" <[email protected]> wrote in message
news:[email protected]...
> 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)

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
>
>

=(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)
> >
> >
>
>

In the example formula I have given H7 is the cell with the total hours in it.

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)
> >
> >
>
>