You can manipulate the formula/reference by using a combination of
absolute/relative references or populating the addresses in a column and then
use INDIRECT... There are many ways you can achieve your requirement.

What I normally do is use a cell on the sheet to hold the Sheet name and
refer to that so that even if a copy is created I don't have to change
anything... INDIRECT and VLOOKUP in combination are very effective.

Any reference will refer to the address you get after replacing all formulae
with thier results...

"Greg in CO" wrote:

Thanks Sheeloo and Roger...both options would be great, however:

- With Pivot tables, my end users go fetal and hide under their desks

- For the Indirect, wouldn't the cell reference on the Indirect-referenced
worksheet be "locked" in the formula, thus preventing me from "dragging" the
formula across the remaining columns or pasting it into rows for other
projects?

There will be several departments and many, many referenced projects. I've
used INDIRECT before, but only to make references to other worksheets dynamic.

Is there way to replace the "DepartmentA" reference with a reference to the
current sheet? I am guessing that any cell reference within the
SUMPRODUCT(--(X)) [X=cell ref location in the formula] would refer to that
cell on the sheet referenced in the formula and not on the current sheet.

I will look at the websites you have provided in the meantime.

Thanks so much for your patience....I feel i am almost there in getting this
streamlined and it is the last little chunk that is frustrating.

Greg
--
Greg


"Roger Govier" wrote:

Hi Greg

it sounds as though you would benefit from using a pivot table for your
report.
For information on setting up Pivot Tables, take a look at Debra Dalgleish's
site
http://www.contextures.com/tiptech.html
scroll down to the section on Pivot Tables
or
Mike Alexander's video tutorials at
http://www.datapigtechnologies.com/ExcelMain.htm

If you need more help on PT's post back
--
Regards
Roger Govier

"Greg in CO" wrote in message
...
Hi All!

I have a SUMPRODUCT formula that is working, but I would like to
streamline
it from a maintenance point of view.

Here is the formula:

=SUMPRODUCT(--(Actuals!$B$2:$B$50="DepartmentA"),--(Actuals!$F$2:$F$50="Smith
Company"),(Actuals!G$2:G$50))

This formula is in a cell in the January column( Column E), on a row (Row
7)titled "Smith Company Project" on a worksheet titled "DepartmentA".

The Actuals worksheet is populated by HR, listing hours for projects by
project name and by department.

The formula's logic is: Go to the Actuals sheet and lookup occurances of
"DepartmentA" and "Smith Company Project", then sum the corresponding
hours
in E7.

The above formula works fine, but I have to go in and change the
Department
reference and the Project reference for each depatment and project. There
will be many departments and many projects. As if that wasn't enough,
after
HR populates the Actuals sheet, I have to go in and scrub the Projects
info,
as there are many projects at the Smith Company, but all have different
names
with "Smith Company" in them (Smith Company ERP, Finance Reporting - Smith
Co., Smith Co. Int'l, etc.). I have to change all those entries to "Smith
Company".

So, is there a modification where I can:

A - use an absolute cell reference for the Department value (i.e. the cell
on the Department sheet which has the Department's name) and for the
Project
value (the cell with the row title for the project)?

B - have the formula look for entries where the entry may only contain a
partial match for the Project name?

Example: On the worksheet DepartmentA, cell C2 contains the Department's
name: "DepartmentA". Cell A7 contains the name of the overall project
"Smith
Company".

I would like to use the cell ref "C2" instead of "DepartmentA" in the
first
section of the formula and then an argument to look for entries containing
"Smith Company" in the second section of the formula. I have tried using
the
cell ref for the Department and then either "*Smith Company*" or
"*"&A7&"*"
for the Project references. They didn't work. However, when I typed the
exact names and adjusted the entries on the Actuals sheet, everything
summed
just fine.

This modified formula also needs to be able to be "dragged" across the
columns for Feb-Dec.

Thank in advance for any assistance!!! :)
--
Greg