Hi all,

So I've got a new problem. Kinda like my last one, but a little easier (I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed. The
information is located in a diffenent workbook called 'JuneOT' (JulyOT for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the DeptOT
workboot, then sums the hours worked if a match occurrs. The function works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT looks and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them again, but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little easier (I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed. The
information is located in a diffenent workbook called 'JuneOT' (JulyOT for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the DeptOT
workboot, then sums the hours worked if a match occurrs. The function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it. Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little easier (I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed. The
information is located in a diffenent workbook called 'JuneOT' (JulyOT for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the DeptOT
workboot, then sums the hours worked if a match occurrs. The function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it. Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed.
The
information is located in a diffenent workbook called 'JuneOT' (JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the
JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

Hey Biff,

thanks again. Sorry about the formula I gave you, yes you were right there
was a mistake (should have been $A$2:$A$27)

I tried your SUMPRODUCT formula and it worked. But were would I insert the
full file path in the formula for it to work? June.xls can be found in:

E:\OT Sheet\June.xls

Thanks,
Matt


"Biff" wrote:

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it. Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug, etc...
I use a SUMIF function in ColumnC to find the the information needed.
The
information is located in a diffenent workbook called 'JuneOT' (JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the
JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

Try this:

=SUMPRODUCT(--('E:\OT Sheet\[June.xls]Sheet1'!$A$2:$A$27=A4),'E:\OT
Sheet\[June.xls]Sheet1'!$G$2:$G$27)

Biff

"mpenkala" wrote in message
...
Hey Biff,

thanks again. Sorry about the formula I gave you, yes you were right
there
was a mistake (should have been $A$2:$A$27)

I tried your SUMPRODUCT formula and it worked. But were would I insert
the
full file path in the formula for it to work? June.xls can be found in:

E:\OT Sheet\June.xls

Thanks,
Matt


"Biff" wrote:

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is
actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is
closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it.
Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little
easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has
been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug,
etc...
I use a SUMIF function in ColumnC to find the the information
needed.
The
information is located in a diffenent workbook called 'JuneOT'
(JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The
function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest
data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the
JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

Hey Biff,

thanks, the formula works great. I had a little trouble from the single
quotation marks but when I wrote it out without the quotations, the computer
simply added them in and its been working great since then.

Matt


"Biff" wrote:

Try this:

=SUMPRODUCT(--('E:\OT Sheet\[June.xls]Sheet1'!$A$2:$A$27=A4),'E:\OT
Sheet\[June.xls]Sheet1'!$G$2:$G$27)

Biff

"mpenkala" wrote in message
...
Hey Biff,

thanks again. Sorry about the formula I gave you, yes you were right
there
was a mistake (should have been $A$2:$A$27)

I tried your SUMPRODUCT formula and it worked. But were would I insert
the
full file path in the formula for it to work? June.xls can be found in:

E:\OT Sheet\June.xls

Thanks,
Matt


"Biff" wrote:

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is
actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is
closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it.
Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little
easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has
been
renamed 'Comms June-Dec2006'. On this sheet I have the following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug,
etc...
I use a SUMIF function in ColumnC to find the the information
needed.
The
information is located in a diffenent workbook called 'JuneOT'
(JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The
function
works
great - that's not my problem - it's after I save and close excel...

Once I close excel and then try to open DeptOT I get the following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest
data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be updated.
2 - All of my SUMIF cells show #VALUE until I actually open up the
JuneOT,
JulyOT...etc. and then close them again. Once this is done, DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt

You're welcome. Thanks for the feedback!

Biff

"mpenkala" wrote in message
...
Hey Biff,

thanks, the formula works great. I had a little trouble from the single
quotation marks but when I wrote it out without the quotations, the
computer
simply added them in and its been working great since then.

Matt


"Biff" wrote:

Try this:

=SUMPRODUCT(--('E:\OT Sheet\[June.xls]Sheet1'!$A$2:$A$27=A4),'E:\OT
Sheet\[June.xls]Sheet1'!$G$2:$G$27)

Biff

"mpenkala" wrote in message
...
Hey Biff,

thanks again. Sorry about the formula I gave you, yes you were right
there
was a mistake (should have been $A$2:$A$27)

I tried your SUMPRODUCT formula and it worked. But were would I insert
the
full file path in the formula for it to work? June.xls can be found
in:

E:\OT Sheet\June.xls

Thanks,
Matt


"Biff" wrote:

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

That formula doesn't make any sense. Are you sure that formula is
actually
doing what you want it to?

The first argument should be a range of cells equal in size to
Sheet1!$G$2:$G$27. It should look like this:

=SUMIF([June.xls]Sheet1!$A$2:$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)

Sumproduct will not work with unequal range sizes. This is the
Sumproduct
version:

=SUMPRODUCT(--([June.xls]Sheet1!$A$2:$A$27=A4),[June.xls]Sheet1!$G$2:$G$27)

You'll have to put in the full path for it to work when June.xls is
closed.

Biff

"mpenkala" wrote in message
...
Hi Biff,

I've never used SUMPRODUCT before so I'll need some help using it.
Here's
the SUMIF formula I was using...

=SUMIF([June.xls]Sheet1!$A$27,A4,[June.xls]Sheet1!$G$2:$G$27)


Thanks for your help,
Matt


"Biff" wrote:

Any ideas or comments??

Don't use SUMIF. Use SUMPRODUCT instead.

If you're not sure how to use Sumproduct post your Sumif formula
and
we'll
translate it into Sumproduct.

Biff

"mpenkala" wrote in message
...
Hi all,

So I've got a new problem. Kinda like my last one, but a little
easier
(I
hope).

I currently have a main workbook called 'Dept OT' and Sheet1 has
been
renamed 'Comms June-Dec2006'. On this sheet I have the
following:
Column 'A' - Employee ID#
Column 'B' - Employee Name
Column 'C' - Total OT (Monthly June)
Column 'D' - Total OT (Yearly)
Column 'E' - Percentage OT Monthly
Column 'F' - Percentage OT Yearly

I then have a empty column and repeat the process for July, Aug,
etc...
I use a SUMIF function in ColumnC to find the the information
needed.
The
information is located in a diffenent workbook called 'JuneOT'
(JulyOT
for
July, AugOT for August... etc).

JuneOT is simply names, ID#, and OT hours worked.
Column 'A' - ID #
Column 'B' - name
Column 'C' - Date
Column 'D' - Hours worked

My SUMIF simply looks for the ID#, matches it with the ID# in the
DeptOT
workboot, then sums the hours worked if a match occurrs. The
function
works
great - that's not my problem - it's after I save and close
excel...

Once I close excel and then try to open DeptOT I get the
following
message:

"This workbook contains links to other data sources.
-If you udate the links, Excel will attempt to retreve the latest
data.
-If you don't update the links, Excel will use the previous
information.

If I try and Update I get 2 possible problems.

1 - The workbook contain one or more links that cannot be
updated.
2 - All of my SUMIF cells show #VALUE until I actually open up
the
JuneOT,
JulyOT...etc. and then close them again. Once this is done,
DeptOT
looks
and
works fine.

Any ideas or comments?? I'm lost on this. I even tried to
create a
macro
that would open up JuneOT, JulyOT, AugOT, etc and then close them
again,
but
it always closes DeptOT as well... so I guess I'm stuck.

Thanks,
Matt