Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
holidays
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
holidays
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
holidays
=(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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
holidays
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" 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Holidays | Excel Worksheet Functions | |||
Number of Week Days _including_ Holidays | Excel Worksheet Functions | |||
Schedule to exclude weekends and holidays | Excel Discussion (Misc queries) | |||
Skip the Holidays 2 | Excel Discussion (Misc queries) | |||
Skip the holidays | Excel Discussion (Misc queries) |