Home 
Search 
Today's Posts 
#1




Desperately need help
I have the following; 
3 sheet workbook, Sheet 1 called Front Page. Sheet 2 called Home and sheet 3 called A. Sheet 1 (Front Page) is just a sheet with instructions. On sheet 2 (Home) is the results of entries made on Sheet 3 (A). I make the following entries on Sheet 2. A6 to A222 are names which transfer onto Sheet 3 automatically using =A6 etc going across the sheet from E7 to IT7. Sheet 1 B2 to B222 is have a hyperlink to jump to a name on sheet 3 which is =HYPERLINK("#A!R6C"&MATCH(A6,A!$D$7:$IS$7,0)+3,"ju mp") On sheet 3 (A) I enter either of the following: d or hd for holidays the result going into E1, =SUMPRODUCT((E7:E2038="d"))(SUMPRODUCT ((E7:E2038="HD"))*0.5) L for lateness going into cell E2, =SUMPRODUCT((E7:E2038="L")) 1 for absence going into cell E3, =SUMPRODUCT((E7:E2037=1), (E8:E2038<1),($A7:$A2037=TODAY()365)) which counts each 1 and blocks of 1. Au or AD going into cell E4, for full day or half day authorised, =SUMPRODUCT((E7:E2038="AU"))(SUMPRODUCT((E7:E2038="AD"))*0.5) All this works great but, as with all good things someone has come up with something new to add. Along with the 1 I now need to enter either 1 p or 1 up. The result of 1 still needs to do the same as I have now. The result of p needs to go into E5, the result of up into E6. I have been told this can be done using a macro and entering something like 1,p. Can anyone give me any help with this please. I am at a loss. Bryan. 
#2




Desperately need help
Hi,
What exactly do you mean by "The result of p needs to go into E5, the result of up into E6." How about in E5 type p or in E6 type up? or in E5 enter =some cell where p is and E6 enter = some cell where up is? Thanks, Shane Devenshire " wrote: I have the following;  3 sheet workbook, Sheet 1 called Front Page. Sheet 2 called Home and sheet 3 called A. Sheet 1 (Front Page) is just a sheet with instructions. On sheet 2 (Home) is the results of entries made on Sheet 3 (A). I make the following entries on Sheet 2. A6 to A222 are names which transfer onto Sheet 3 automatically using =A6 etc going across the sheet from E7 to IT7. Sheet 1 B2 to B222 is have a hyperlink to jump to a name on sheet 3 which is =HYPERLINK("#A!R6C"&MATCH(A6,A!$D$7:$IS$7,0)+3,"ju mp") On sheet 3 (A) I enter either of the following: d or hd for holidays the result going into E1, =SUMPRODUCT((E7:E2038="d"))(SUMPRODUCT ((E7:E2038="HD"))*0.5) L for lateness going into cell E2, =SUMPRODUCT((E7:E2038="L")) 1 for absence going into cell E3, =SUMPRODUCT((E7:E2037=1), (E8:E2038<1),($A7:$A2037=TODAY()365)) which counts each 1 and blocks of 1. Au or AD going into cell E4, for full day or half day authorised, =SUMPRODUCT((E7:E2038="AU"))(SUMPRODUCT((E7:E2038="AD"))*0.5) All this works great but, as with all good things someone has come up with something new to add. Along with the 1 I now need to enter either 1 p or 1 up. The result of 1 still needs to do the same as I have now. The result of p needs to go into E5, the result of up into E6. I have been told this can be done using a macro and entering something like 1,p. Can anyone give me any help with this please. I am at a loss. Bryan. 
#3




Desperately need help
On 22 Nov, 21:19, Shane Devenshire
wrote: Hi, What exactly do you mean by "The result of p needs to go into E5, the result of up into E6." *How about in E5 type p or in E6 type up? or in E5 enter =some cell where p is and E6 enter = some cell where up is? * Thanks, Shane Devenshire " wrote: I have the following;  3 sheet workbook, Sheet 1 called Front Page. Sheet 2 called Home and sheet 3 called A. Sheet 1 (Front Page) is just a sheet with instructions. On sheet 2 (Home) is the results of entries made on Sheet 3 (A). I make the following entries on Sheet 2. A6 to A222 are names which transfer onto Sheet 3 automatically using =A6 etc going across the sheet from E7 to IT7. Sheet 1 B2 to B222 is have a hyperlink to jump to a name on sheet 3 which is * =HYPERLINK("#A!R6C"&MATCH(A6,A!$D$7:$IS$7,0)+3,"ju mp") On sheet 3 (A) I enter either of the following: d or hd for holidays the result going into E1, *=SUMPRODUCT((E7:E2038="d"))(SUMPRODUCT ((E7:E2038="HD"))*0.5) L for lateness going into cell E2, =SUMPRODUCT((E7:E2038="L")) 1 for absence going into cell E3, *=SUMPRODUCT((E7:E2037=1), (E8:E2038<1),($A7:$A2037=TODAY()365)) which counts each 1 and blocks of 1. Au or AD going into cell E4, for full day or half day authorised, =SUMPRODUCT((E7:E2038="AU"))(SUMPRODUCT((E7:E2038="AD"))*0.5) All this works great but, as with all good things someone has come up with something new to add. Along with the 1 I now need to enter either 1 p or 1 up. The result of 1 still needs to do the same as I have now. The result of p needs to go into E5, the result of up into E6. I have been told this can be done using a macro and entering something like 1,p. Can anyone give me any help with this please. I am at a loss. Bryan. Hide quoted text   Show quoted text  Thanks for answering Shane, I knew I'd mess it up what I was trying to say. In E5 I need the count of P's in that column in E5. The UP in E6. Each row going down from A8 to A2037 has the date up to 2015. So I need a count of either the P or up, but I also have to put a 1 in the same cell. I count the 1's okay, but I can't figure how to count the P etc at the same time. At the moment if I enter 1, p neither counts. Have I explained it better? Bryan. 
#4




Desperately need help
On 22 Nov, 22:28, wrote:
On 22 Nov, 21:19, Shane Devenshire wrote: Hi, What exactly do you mean by "The result of p needs to go into E5, the result of up into E6." *How about in E5 type p or in E6 type up? or in E5 enter =some cell where p is and E6 enter = some cell where up is? * Thanks, Shane Devenshire " wrote: I have the following;  3 sheet workbook, Sheet 1 called Front Page. Sheet 2 called Home and sheet 3 called A. Sheet 1 (Front Page) is just a sheet with instructions. On sheet 2 (Home) is the results of entries made on Sheet 3 (A). I make the following entries on Sheet 2. A6 to A222 are names which transfer onto Sheet 3 automatically using =A6 etc going across the sheet from E7 to IT7. Sheet 1 B2 to B222 is have a hyperlink to jump to a name on sheet 3 which is * =HYPERLINK("#A!R6C"&MATCH(A6,A!$D$7:$IS$7,0)+3,"ju mp") On sheet 3 (A) I enter either of the following: d or hd for holidays the result going into E1, *=SUMPRODUCT((E7:E2038="d"))(SUMPRODUCT ((E7:E2038="HD"))*0.5) L for lateness going into cell E2, =SUMPRODUCT((E7:E2038="L")) 1 for absence going into cell E3, *=SUMPRODUCT((E7:E2037=1), (E8:E2038<1),($A7:$A2037=TODAY()365)) which counts each 1 and blocks of 1. Au or AD going into cell E4, for full day or half day authorised, =SUMPRODUCT((E7:E2038="AU"))(SUMPRODUCT((E7:E2038="AD"))*0.5) All this works great but, as with all good things someone has come up with something new to add. Along with the 1 I now need to enter either 1 p or 1 up. The result of 1 still needs to do the same as I have now. The result of p needs to go into E5, the result of up into E6. I have been told this can be done using a macro and entering something like 1,p. Can anyone give me any help with this please. I am at a loss. Bryan. Hide quoted text   Show quoted text  Thanks for answering Shane, I knew I'd mess it up what I was trying to say. In E5 I need the count of P's in that column in E5. The UP in E6. Each row going down from A8 to A2037 has the date up to 2015. So I need a count of either the P or up, but I also have to put a 1 in the same cell. I count the 1's okay, but I can't figure how to count the P etc at the same time. At the moment if I enter 1, p neither counts. Have I explained it better? Bryan. Hide quoted text   Show quoted text  I think now the easiest way to ask this, is this way. I use this fornula to count 1's and blocks of 1's when I enter 1 into cells. =SUMPRODUCT((E7:E2037=1),(E8:E2038<1),($A7:$A2037=TODAY ()365)) This works fine. The result of the formula goes into cell E3. Now I need to add a P or UP to the same cell I enter a 1 into. When I enter 1 P or 1 UP that formula dosen't work. How can I isolate my formula so that it does work, and add another formula so it counts the P or UP but ignores the 1 so that my original formula can do its thing. Not all cells will have the same entered. I've tried many things with countif statements etc. I'm getting nowhere fast. I would be greatful if anyone can point me in the right direction. Bryan. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Desperately in need of some help  New Users to Excel  
Desperately need a command or something!  Excel Discussion (Misc queries)  
DESPERATELY NEED HELP  Excel Discussion (Misc queries)  
Desperately need help!!  New Users to Excel  
Desperately need help with 3 calculations  Excel Worksheet Functions 