Named Range is affected by cell movement.

niteshp112

New Member
Joined
Mar 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Cursor at B20.PNG
    Cursor at B20.PNG
    40.7 KB · Views: 4
  • Cursor at B21.PNG
    Cursor at B21.PNG
    43.2 KB · Views: 4

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,789
Messages
5,638,318
Members
417,020
Latest member
MSVII

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top