Ok, here's the current formula, but I'm pretty sure I've uncovered a
larger issue, which may involve a macro I think...
=COUNTIF('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES") I've tried changing it to what you suggested,
=sumproduct(--('C:\Documents and Settings\Peter\My
Documents\PRK\[Payroll_Master_Redding_2004.XLS]OCT
04'!$B$8:$B$22,"YES")) - it didn't seem to like either the -- or the
extra set of brackets... it's running on Excel 2000...
But before you expend wasted effort, the larger issue is the automatic
update of links. Here's the current process...
Techs enter their times on the Timesheet workbook which is colocated
in a folder with their Payroll Master workbook. They are SUPPOSED to
open their Payroll Master to verify that everything is right, but
they generally don't - which means that the values in the Payroll
Master don't update.
Each tech has their own folder. In a separate folder I have the
Payroll Entry/Paycheck Calculator workbook that Managers and
Accounting have access to to enter Additional time, PTO, ect. which
populates cells on each tech's Payroll Master. It also uses the
Payroll Master as a source so that they have easy viewing of
pertinent information without opening each tech's Payroll Master. One
final note, Each Payroll Master is named the same (which I can change
if necessary - I know you can't have multiple files open with the
same name).
The issue here is that the techs will enter their info but not update
the links on the Payroll Master. When Accounting and Managers go to
enter their data, the reference data is not updated because the
Payroll Master hasn't updated.
I hope this hasn't totally lost you, and yes I'm sure there was a
better way of constructing this whole thing, but I started out not
knowing anything about Excel a year ago...
I'm guessing there's a macro that can autoupdated all related links
thru multiple workbooks?
On another note, for some odd reason the links in Payroll Master keep
losing connection to Payroll Entry. Every time I open Payroll Master
and update links (on opening) it says it can't find "Payroll Entry"
and I have to manually tell it where to go. Any suggestions?
Hi
post your current COUNTIF formula. It is then very simple to convert
it to a working SUMPRODUCT formula. e.g.
=COUNTIF('c:\temp\[testbook.xls].sheet'!A1:A100,"condition")
should be converted to:
=SUMPRODUCT(--('c:\temp\[testbook.xls].sheet'!A1:A100="condition"))
--
Regards
Frank Kabel
Frankfurt, Germany
pkley wrote:
I have multiple workbooks with multiple sheets (don't ask...)
Anyway, I have a paycheck calculator that does all the math for
OT/DT etc. that works great.
However, if the tech is on call, he marks a X in the On Call box
which in turn makes another workbook cell say Yes.
In my third paycheck workbook I'm using a Countif "yes" to total the
number of days the tech was on call and then multiply that by the on
call pay. This is the only link that doesn't work and I recall
reading that countif doesn't work if the source isn't open (which is
generally the case).
Any suggestions? A macro? A different tactic?
The dates are in one column, and the "Yes" are in another column.
Possibly returning a number instead of yes?
Help! Sick and brain quit working!
