I have a spreadsheet which is well-used within my company, and I need to get users to add in a new reporting sheet. This is fairly straightforward using Edit | Move or Copy Sheet… from a new master file that they can access. There are links to be re-directed from the Master sheet to their file but thats fairly easy for them too.
What causes them some problems is that there are lots of named ranges used in the original spreadsheet which the new reporting sheet links into. But when the new sheet is copied into their workbook, the ranges are unique to the new sheet (i.e. in the Define Name list, they have the name of the new sheet on the right) and in most cases, the cells referenced are incorrect. (This is because although the same name exists in both their spreadsheet and the master, the ranges refer to different cells because they have expanded their spreadsheet in the course of their work). The only way that I know of correcting this is to have them go down the Define Name list and delete all the offending names (which then leaves the same name on the list, but no longer unique to the new sheet). Some users find this difficult.
Does anyone know a better way of deleting the "unique" names en masse. Perhaps a macro that I could create for them to use?
Thanks in advance,
DuckBill
What causes them some problems is that there are lots of named ranges used in the original spreadsheet which the new reporting sheet links into. But when the new sheet is copied into their workbook, the ranges are unique to the new sheet (i.e. in the Define Name list, they have the name of the new sheet on the right) and in most cases, the cells referenced are incorrect. (This is because although the same name exists in both their spreadsheet and the master, the ranges refer to different cells because they have expanded their spreadsheet in the course of their work). The only way that I know of correcting this is to have them go down the Define Name list and delete all the offending names (which then leaves the same name on the list, but no longer unique to the new sheet). Some users find this difficult.
Does anyone know a better way of deleting the "unique" names en masse. Perhaps a macro that I could create for them to use?
Thanks in advance,
DuckBill