Excel 2007 : Defined Name of Data Range in Graph Changed When Spreadsheet is Saved

[gav0101]

Hi all,

I'm looking for some help with why the spreadsheet name is being changed to [0], everytime I close the workbook.

In Excel 2003 I built a dynamic graph spreadsheet using defined names, date cells, and the offset function. I'm now trying to convert the spreadsheet to Excel 2007. I opened the spreadsheet in Excel 2007, saved it as xlsm 2007 spreadsheet.

The data range on the graphs consist of the qualified spreadsheet name followed by a define name that represents a column of data in the data worksheet. When I converted to 2007, all the qualified spreadsheet names were changed to [0]. My problem is that when I change the [0] back to the spreadsheet name, save and close the spreadsheet, then open it, the spreadsheet name is changed back to [0]. I can't get Excel to retain the spreadsheet name in the data range name of the graph.

Here are the steps I've been using.

1. Change either or both of the dates in row 3 of the "Parms" worksheet.

2. The date period of the graph in the "Daily Images" worksheet should change to the selected dates, but it doesn't.

3. In the "daily Images" worksheet I bring up the selected data. The define name is preceeded by [0]. I change [0] to the spreadsheet name. The graph begins to work and changes when I change the date parameter.

4. I save and close the worksheet.

5. I open the worksheet and the spreadsheet name in the graph data ranges is back to [0]

[pike]

Hi gav0101
The file is a read only

[gav0101]

Hi Pike.

What makes you say (how can I tell) the file is read only? I don't see anything on my screen that says read only.

Excel appears to be allowing me to make the change, It doesn't tell me the file is read only when I save the spreadsheet.

[pike]

Hmm. its a pain

xcan you just leave it in compatable 2003 mode?

[gav0101]

The answer to your question is no. I have to convert it to 2007 because I want to take advantage of some 2007 functionality.

I've resolved my problem, which I originally thought were two separate issues.

1. When I would save a *.xls file as a *.xlsx or */xlsm, I would lose most of the dynamic graphs that I had built. See thread "Graphs Not Displaying Correctly After Conversion from 2003 to 2007 Excel ". The graphs were there, just not visable.

2. This thread was related to the graphs that were visable.

The solution turned out to be applying both Windows xp and Office 2007 patches. I went out and brought my system up to date on all patches. Once I did that, the all the graphs were visable and working. When I converted the spreadsheets to 2007, the spreadsheet name used to qualify the defined name was updated with the new suffix on the spreadsheet name (.xlsx or .xlsm). All the graphs were visable and working fine. I'n now moving ahead with the upgrade to Office 2007.