Defined names in data series formula become converted to cells after copying sheet

[Deridjian]

Well hello everybody,

the title is very descriptive I hope. I wanted to have a pretty simple (1 data row), but dynamic, chart. So I used a very neat solution I found on the web earlier, in which I made a separate cell with

Please Login or Register  to view this content.

This apparently counts the cells that have content until row 1000. After this I had to define two names with the name manager for my x and y axis. One is

Please Login or Register  to view this content.

and the other one just alike.

Please Login or Register  to view this content.

So whenever I add or delete a row in the range in between the first given cell and row 1000 the chart live adds/removes the data. Pretty sweet.

However the problem to this comes when I copy the whole work sheet to make another calculation for a different project. After copying (move-copy) the work sheet the defined names that in the source sheet are being shown in the formula window get converted into an actual cell range (i.e. MYNAME gets converted to $A$1:$A$28). This breaks the whole process of live adding/removing the data, as the chart now doesn't refer to the COUNT function but rather to an actual given cell range.

Is this a known bug? Is there a solution to this? Anybody with experiences with this?
I'd appreciate any comments/ideas/workarounds on this. I added a sample file testdummy.xlsx for this which recreates the error, feel free to play around with it.

Sincerely

Deridjian

[rajeshturaha]

Hi Deridjian
Welcome to the forum.
i think, instead of copying the sheet, you can use move or copy sheet to a new workbook.
This will not break the defined name to range

[Deridjian]

Dear Rajeshturaha (Awesome name, is that your real one?)

thank you for your consideration. I probably didn't make myself clear enough. I would never actually copy/paste a whole worksheet. I thought this was just natural for everybody. Well now, if you do what you said, and what I wanted to say, that is, move-copy the sheet, you will definitely reproduce this error. At least in Excel 2011 OSX that is. Maybe it's a platform thing? I wouldn't know why it should be though.
Please give me feedback if you have success with the dummy file (I actually rewrote the copy phrase in the file to make this more clear) without breaking the names in the SERIES function of the chart. And if so, on what system/version that was.
Thank you for taking some of your time for me. Really appreciate it.

Edit: After reading your post again, did you want to tell me to actually move my sheet to a new file (workbook)? Sorry if I didn't get that right first? That would totally destroy the purpose of copying formulas of existing worksheets in the same workbook tho, which is what I need and I think is a daily Excel use case, isn't it? :S

Edit2: I just tried that as well and it happens to reproduce the exact same behavior. It just has got to do with copying the names in a SERIES formula, no matter the way you do it.

[rajeshturaha]

Deridjian
Thank u for correcting me. After googling i found that the basic format for using name range in chart is =fileName!Name_Of_Range).
It is taking the sheet name along with the range name. So when you are copying or moving the sheet in a new workbook the file name changes. That is why the range name is converted to range.

Plz update if u have any other solution