Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 22nd 08, 09:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 27
Default 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   Report Post  
Old November 22nd 08, 10:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default 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   Report Post  
Old November 22nd 08, 11:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 27
Default 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   Report Post  
Old November 23rd 08, 08:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 27
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Desperately in need of some help perfj New Users to Excel 2 April 8th 08 09:02 PM
Desperately need a command or something! mitch phelps Excel Discussion (Misc queries) 5 February 2nd 08 09:03 AM
DESPERATELY NEED HELP newuser Excel Discussion (Misc queries) 3 November 2nd 07 05:02 PM
Desperately need help!! Paula_p New Users to Excel 1 June 13th 06 10:26 PM
Desperately need help with 3 calculations Scoooter Excel Worksheet Functions 3 June 12th 06 04:28 PM


All times are GMT +1. The time now is 09:29 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017