| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
even/odd returns 1 0r 16
HI!
Thanks for all the help in the very recent past! I have a new item that I would like to do... In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? Thanks in advance for looking at my posts! The current project looks very amazing thanks to you! Greg |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Hi
see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Thanks Frank!
This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Hi
just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
FANTASTIC!
THANKS Frank! I really do appreciate your help! Greg "Frank Kabel" wrote in message ... Hi just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Sorry... but one more(ish)
I'd like to highlight the range W9:X134 when there is a Holiday in the pay period... I can get it to work for the first day of the pay period but not if the holiday falls on any other day... What am I missing? Greg "Gbiwan" wrote in message ... FANTASTIC! THANKS Frank! I really do appreciate your help! Greg "Frank Kabel" wrote in message ... Hi just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Hi
not sure what kind of data is stored in you ranges. Can you give some examples -- Regards Frank Kabel Frankfurt, Germany "Gbiwan" schrieb im Newsbeitrag ... Sorry... but one more(ish) I'd like to highlight the range W9:X134 when there is a Holiday in the pay period... I can get it to work for the first day of the pay period but not if the holiday falls on any other day... What am I missing? Greg "Gbiwan" wrote in message ... FANTASTIC! THANKS Frank! I really do appreciate your help! Greg "Frank Kabel" wrote in message ... Hi just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
It would help if I was more specific eh? I'm trying to use conditional
formatting to change the color of the range if there is a STAT holiday in the pay period. (which is what the previous postings gave me... Thanks again by the way!) I can figure out if "E6" is the holiday but I can't figure out how to get the formula to look for any holiday that is returned on the other days of the pay period... the days run from "E6" to "T6" does this make sense? Thanks in advance for your patient help! Greg "Frank Kabel" wrote in message ... Hi not sure what kind of data is stored in you ranges. Can you give some examples -- Regards Frank Kabel Frankfurt, Germany "Gbiwan" schrieb im Newsbeitrag ... Sorry... but one more(ish) I'd like to highlight the range W9:X134 when there is a Holiday in the pay period... I can get it to work for the first day of the pay period but not if the holiday falls on any other day... What am I missing? Greg "Gbiwan" wrote in message ... FANTASTIC! THANKS Frank! I really do appreciate your help! Greg "Frank Kabel" wrote in message ... Hi just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
even/odd returns 1 0r 16
Hi
still not sure what you're trying to achieve :-) If you like email me your spreadsheet (frank[dot]kabel[at]freenet[dot]de) and i'll have a look at it. -- Regards Frank Kabel Frankfurt, Germany "Gbiwan" schrieb im Newsbeitrag ... It would help if I was more specific eh? I'm trying to use conditional formatting to change the color of the range if there is a STAT holiday in the pay period. (which is what the previous postings gave me... Thanks again by the way!) I can figure out if "E6" is the holiday but I can't figure out how to get the formula to look for any holiday that is returned on the other days of the pay period... the days run from "E6" to "T6" does this make sense? Thanks in advance for your patient help! Greg "Frank Kabel" wrote in message ... Hi not sure what kind of data is stored in you ranges. Can you give some examples -- Regards Frank Kabel Frankfurt, Germany "Gbiwan" schrieb im Newsbeitrag ... Sorry... but one more(ish) I'd like to highlight the range W9:X134 when there is a Holiday in the pay period... I can get it to work for the first day of the pay period but not if the holiday falls on any other day... What am I missing? Greg "Gbiwan" wrote in message ... FANTASTIC! THANKS Frank! I really do appreciate your help! Greg "Frank Kabel" wrote in message ... Hi just enter in the cell for column E the following formula: =IF(COUNTIF($X$1:$X$100,E$6)=1,"STAT","") copy this to the right -- Regards Frank Kabel Frankfurt, Germany Gbiwan wrote: Thanks Frank! This did the trick! Is there a way to format a cell in the row below based on the result of the conditional formatting? (or countif formula?) I'd like to return STAT in the cell directly below the conditionally formatted holiday result... any ideas? Thanks again for your great HELP! TTFN Greg "Frank Kabel" wrote in message ... Hi see below In cell "E6" I would like to return either 1 or 16 based on the result of a lookup table in cell "DB4". The easiest would be if the answer was even then "16" would be returned. If it was odd then "1". I know that there is an easy solution but can't get me head around it... try =IF(MOD(your_lookup_formula,2)=0,16,1) What I'm trying to do is to change the dates on a payroll sheet... this seems like the easiest way to do so... however if somebody could tell me a way to populate the columns to the right of the first day of the pay period ("E6") based on a period picked from a control that would be even better! not quite sure what you're trying to achieve but if you insert a date in E6 you can use the following in F6 =E6+1 - included weekends or =WORKDAY(E6,1) - if you want only workdays copy both to the right Then I think that there's a way to mark and identify Holidays correct? In the perfect world... the user would pick a pay period... the cells "E6" to "T6" would fill in the dates and if there was a holiday during that period that the column would be highlighted... could that be done? one way: - you have stored your holiday dates in a separate range (lets say X1:X100) - highlight the columns E6:T6 - goto 'format - conditional format' and enter the following formula =COUNTIF($X$1:$X$100,E$6)=1 - choose your format for the holiday columns Frank |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index with mulitple value returns and muliple column returns | Excel Worksheet Functions | |||
| IRR returns DIV/0 | Excel Discussion (Misc queries) | |||
| Date returns always returns: 00 January 1900 | Excel Worksheet Functions | |||
| SUM IF returns a zero value | Excel Worksheet Functions | |||
| cell with value returns that value, empty cell returns zero | Excel Worksheet Functions | |||
Linear Mode
