I have a spreadsheet the lists current projects and employees working on that
project. It also contains calculations to calculate how many employees are
on the site, how many more needed, how many extra are on site, etc. The
formula contains named ranges for each category of emp


Job ABC = project 21
SMEmployees = named range SME21
SMAEmployees = named range SMA21
etc.

My problem is that the people using the spreadsheet drag the name around
from project to project and it seems that it is screwing up the references
for the named ranges and in turn screwing up the formula. Is there a way to
set the ranges as absolute. I have asked them to copy and paste, but it is
beyond them.

Thanks for any help you can provide!!!!!

You shoud have a look at the INDIRECT worksheet function. I use it every now
and again, but it makes formulas rather longer than usual, plus it won't ever
look into files that are not opened and return a #REF error if you have your
function looking into another workbood that is not opened. Otherwise, if
your reference is to the same workbook, it will never become problematic and
people could drag and drop whatever they want, the formula will not follow
the cells in their new destinations.

It will not automatically update a worksheet name if you happen to change
one, though, so be carefull if your formula refers to another worksheet from
within your workbook. If your formula refers to the same worksheet, of
course, it will not be a problem.

Hope it helped,
Félix

"SMBR" wrote:

I have a spreadsheet the lists current projects and employees working on that
project. It also contains calculations to calculate how many employees are
on the site, how many more needed, how many extra are on site, etc. The
formula contains named ranges for each category of emp


Job ABC = project 21
SMEmployees = named range SME21
SMAEmployees = named range SMA21
etc.

My problem is that the people using the spreadsheet drag the name around
from project to project and it seems that it is screwing up the references
for the named ranges and in turn screwing up the formula. Is there a way to
set the ranges as absolute. I have asked them to copy and paste, but it is
beyond them.

Thanks for any help you can provide!!!!!