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),FI ND("]",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