defined names corrupt after saving

[jless]

After saving my excel file the names I defined using "Insert>Name>Define..." become corrupted. The name changes from what I entered
=OFFSET(historical!F$9,0,0,COUNT(historical!$F$9:$F$300))


to this =OFFSET(historical!M$9,0,0,COUNT(historical!$F$9:$F$300))

I am using the defined names to make dynamic range charts. Each time after I save excel corrupts the defined names which clears out the data I have on my dynamic ranging charts.

It always changes the part in red to something random like "P" or "XJM" for example. Any help would be greatly appreciated!!

I am using Excel 2010

 

[Mohammad Basem]

Welcome to the forum!

Actually, nothing is corrupted!

An important point to consider when defining names having relative cell reference(s) is to select the cell where the name will be used then define the name.

Why?

Think of it like entering a formula. Considering the example in your post, if the formula is entered in the original location it will have the first form. After that if it has been copied to different column the references will be adjusted as per the new location (the second form). Same happens with names.

To check whether the name is really corrupted or not, select the original cell, where the name was first defined, and check the formula.

 

[jless]

I follow what you are saying, but I am not sure it applies directly to dynamic ranging charts.

After playing around with it for a bit I fixed the problem by simply saving the name as =OFFSET($F$9,0,0,COUNT(historical!$F$9:$F$300))

I did this for all the names and it worked. For some reason leaving off the sheet name "historical!" fixed the problem. Maybe it made the name a global variable for the whole workbook. I'm not sure, but it fixed the problem. All the dynamic ranging charts were working properly even after I saved the file.


Thanks anyways for the help!!!

 

[Mohammad Basem]

Glad to know your problem has been solved. Al-hamdullah.

Maybe it made the name a global variable for the whole workbook.

Excel always adds the sheet reference whether the scope is Workbook or Sheet. I don’t think we can say it becomes a global variable because the sheet reference is added.

Also, you don’t need to sacrifice the relative addressing. Sometimes defined names need adjustment after the have been defined (I don’t know why, but it seems this has happened in your case).

…but I am not sure it applies directly to dynamic ranging charts

Defined names whether static ranges or dynamic ranges can be used in charts or elsewhere. The definition is the same.

 

[jless]

How would one create a name so that is is global or consistent throughout all the sheets? I have tried to find information about it and all I could find is ways to do it using VBA code.

Is their a way to create global names without using code? Would it be better to define my names as global so I can use them on a chart on any sheet without having any conflict?

 

[Mohammad Basem]

How would one create a name so that is is global or consistent throughout all the sheets? I have tried to find information about it and all I could find is ways to do it using VBA code.

To define names that can be used in all sheets make the Scope Workbook (a popup list found when defining the name after clicking Add). And if you want a name to be specific to a Worksheet then select the sheet name in the Scope popup list.

Is their a way to create global names without using code? Would it be better to define my names as global so I can use them on a chart on any sheet without having any conflict?

I am not clear about what you mean here, but if you define a name and make the scope Workbook, then you can use this name in every sheet, keeping in mind that the names will always refer to the source data (ranges).

 

[mikerickson]

Name: OneCell RefersTo: =Sheet1!$A$1
Name: SomeCell RefersTo: =!$A$1

OneCell will always refer to a cell on sheet 1.
SomeCell will always refer to a cell on the sheet where the name is used.

The ! (without a sheet name) acts as a "relative sheet" indicator when used in the definition of a name.

 

[Mohammad Basem]

Thank you mikerickson very much for the "relative sheet" information! Never heard about it.
All the best