Reply to thread

Message: [QUOTE="Hans, post: 11670517"] Hi, I'm facing the following situation: - I've got named ranges using an offset function f.ex. Range1_Sheet1=offset(...), Range1_Sheet2=offset(...),etc pointing to a range of numbers. (The size and position of these range depend on the content of values in another column). - My worksheet names are part of the names of the ranges f.ex. Sheet1, Sheet2,... - I'm using a sum function to count the total value of the numbers in each of these ranges. So far no problem. But... - The point is that when I copy a sheet and change it's name, I would like the function to still work i.e sum the values of the range (I still have to define this separately) on the new sheet. So looking into this forum I found the mid(cell(...)) trick to get the worksheet name. This combined with sum(indirect()) gives =SUM(INDIRECT("Range1_"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))) But this does not work. I think it is linked to the offset function in the range names. In debugging I discovered that it works when defining an range name without using offset. Is this a limitation of excel or am I overlooking something? Thanks, Hans [/QUOTE]