Named Range is affected by cell movement.

[niteshp112]

Hi,

I am facing with an issue in excel which is giving me serious trouble.

There is a named range in my workbook which changes its formula based on cell position.

For example, whenever I am move my cursor from cell A1 to B1, in Sheet1 or any sheet, the named range formula also changes from 'Sheet2'!A1 to 'Sheet2'!B1.

Any help will be appreciated.

I can try and delete this named range but I just ant to understand why this problem occurs since I deal with lots of named ranges in my work

Attached are 2 images, the problem range is highlighted in yellow.

 

[jasonb75]

It's because the reference is relative, if you want it to be fixed then you need to make it absolute. There are 4 different possible combinations of absolute and relative depending on what you need. In order to understand better, I would suggest that you create a named range for each one, then select a different cell and see which parts change.

Relative row, relative column ='Sheet2'!A1
Absolute row, absolute column ='Sheet2'!$A$1
Relative row, Absolute column ='Sheet2'!$A1
Absolute row, relative column ='Sheet2'!A$1

 

[niteshp112]

Thank you so much!

 

[jasonb75]

You're welcome

One additional thing that I forgot to mention in my first reply, relative named ranges can be very useful, but need to be set up properly, but you need to remember that they will always be relative to the cell that is active when the named range is created. This often catches people out when they create relative named ranges, or conditional formatting rules (which work in the same way).

If you select A1, then create a named range that refers to B2, then the named range will always refer to the cell that is one row down and one column right of the selected cell, or any formula that refers to that name.

If you did the reverse, selected B2, then named A1 as a range, it would mean that it refers to the cell 1 row above and one column to the left.