Absolute Named Ranges????
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!!!!! |
Absolute Named Ranges????
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!!!!! |
All times are GMT +1. The time now is 01:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com