Excel 2007 : Named range keeps changing

**Kenny Bones** · 07-08-2011, 07:26 AM

I don't know much about these named ranges. But I do know that I have to use these in order to get what I want. A dynamic chart.

But my problem is that the named range keeps changing by itself.

One of my ranges looks like this:

Please Login or Register  to view this content.

I should also perhaps mention that the cells in E2 to E60 also contains formulas.
This formula basically just copies the value from another spreadsheet, but only if the value in column E in the same row has a value other than nothing or 0.

Strangely, when I open the spreadsheet, I get the option to Update the workbook.
And if I do this, I get an error that the formula is wrong.
And then if I go to check the Named range, it's all changed. Into something I can't even understand. It looks like this right now:

Please Login or Register  to view this content.

What is the reason why named ranges changes like this? I want it to always select the range I'm after, not change it at all. Also, I had some help constructing this formula as well, so I don't know why SUMPRODUCT is even there. Basically, this formula is supposed to include the IF(Tall1!E2:E60<>""). So that the range won't include any empty values.

**JBeaucaire** · 07-08-2011, 07:42 AM

Named ranges are by nature dynamic and the references in the range formula will change based on the cell selected each time you open the definition window. This is often a useful thing.

But in your case you want the named range reference to NOT adjust itself that way. To stop this behavior, you must LOCK the reference with $ symbols, the way you locked the A2 reference by making it $A$2.

=OFFSET(Tall1!$A$2;0;0;SUMPRODUCT(IF(Tall1!$E$2:$E$60<>"";1;0));1)

**Kenny Bones** · 07-08-2011, 07:44 AM

You don't say!

I did not know that! Ok, I'll try it out

**teylyn** · 07-08-2011, 07:49 AM

In the named range defined as per the formula above, the portion referring to Tall1!E2:E60 will change based on the currently active cell.

If you want the range to be consistent, you will need to change Tall1!E2:E60 to Tall1!$E$2:$E$60