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.