Hi all, im trying to get my spreadsheet to count in one cell if certain criteria appear in two other cells, say for instance =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next named range and the number would be from -11 to 11.
So in short i would like the value for "Hours" displayed in Bloggs J cell (which could be lets say E3) only if Bloggs J appears in the named range "Name"
Hope that is clear and hope you can help!
Simon
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
At the moment i have the follwing in the cells, which is great and adds 1 for every instance it sees of the name in the Range "Name" but instead of counting in column B each instance of the name and adding 1 i need it to see the name Bloggs J in the "name" range and then look for the value on the same row in the range "Hours" this will then be totalled with the results from another sheet as you can see in column G.
Any thoughts?
Simon
Column E
Bloggs J
Column F
=COUNTIF(Name,"Bloggs J")
Column G
=SUM(F40,'Holiday Count'!K33)
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
will mail you but it won't be until 6pm GMT
Thanks,
Simon
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Max,
Thanks, i've been working nights so took me a little time to work out what you had proposed and the automation works fine ( a lot smarter too!)......there was one thing i needed the workbook to do, that is when hours are added or deducted on the Xtra's sheet they need to be added or removed from the total on the Holiday Count sheet, i realise that this sort of creates a circular calculation which Excel doesn't like, so maybe i have steered you wrong in the first place as to the totalling system.
The people that use this will at first glance at the holiday count to see if a person has any entitlement left then will use the Holidays sheet to book more hours, however the Xtra's sheet will be used if hours are given or taken for any other reason other than legitimate holidays so the total needs to be shown here too (perhaps its a strange concept to have the 2 totals but it needed to be shown for simplicity for the users).
Hope this clears it up a little.............again thanks for all your help!
Simon.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
You're welcome, Simon !
I'm out of further ideas here to offer you.
Monitor your thread here awhile
for possible insights from other folks
Or you may wish to put in a new post ..
All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
You're welcome, Simon !
I'm out of further ideas here to offer you.
Monitor your thread here awhile
for possible insights from other folks
Or you may wish to put in a new post ..
All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Maybe you mean in E3:
=SUMPRODUCT((Name="Bloggs J")*(Hours=10))
which would return the count you're after in your line
> =COUNTIF(Name,"Bloggs J" and Hours,= number)
assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, im trying to get my spreadsheet to count in one cell if certain
> criteria appear in two other cells, say for instance
> =COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
> named range and the number would be from -11 to 11.
>
> So in short i would like the value for "Hours" displayed in Bloggs J
> cell (which could be lets say E3) only if Bloggs J appears in the named
> range "Name"
>
> Hope that is clear and hope you can help!
>
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=401673
>
If you'd like to, email over a copy of your file at:
demechanik < at > yahoo <dot > com
Think a look at your set-up would be clearer
Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No prob ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Here's my best shot at it, Simon,
after studying your detailed set-up and comments:
In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")
Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:
=SUMIF($A:$A,E2,$D:$D)
(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)
And you have in G2:
=SUM(F2,'Holiday Count'!B5)
but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:
G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)
The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell
Here, think we could try to automate it by changing it in G2 to:
=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))
The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned
Then just select F2:G2, fill down as far as required
And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P
Copy F2:G2, then do a paste special > Check "Formulas" > OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc
Then just select & fill I2:J2 down as far as the extent done for cols F & G
And repeat the process for cols L & M, and cols O & P
I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Simon's cover note:
As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.
This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.
This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.
When the "log in" box pops up just enter <snipped> and it will allow you
to proceed.
You're welcome, Simon !
I'm out of further ideas here to offer you.
Monitor your thread here awhile
for possible insights from other folks
Or you may wish to put in a new post ..
All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks