Excel - need to name a range that will not move. 1

[srogers]

Has anyone used the Indirect function to define a range so that it won't move when rows are added or deleted?

I want to define the range B22:X22 and I want it to stay that range even though they will be inserting rows and deleting them.

That's my only idea - maybe there's another way?

Thank you -

 

[PeterMoran]

Hi srogers,

Can do!

Select the relevant range as normal and then goto Insert Name Define as usual, and provide the Name of the range at the top of the Define Name window.

Then place the cursor in the "Refers to" box and press F2 to edit the present entry which will look something like - "=Sheet1!$B$22:$X$22" without the quotes. This needs to be changed to "=INDIRECT("Sheet1!$B$22:$X$22")" - again no external quotes - and then select OK.

Good Luck!

Peter Moran

 

[srogers]

Peter -
Thanks! That's great - it works. I appreciate your time.
Sharon