How to create an array reference that won't move

  • Thread starter Thread starter John
  • Start date Start date
J

John

How can I create a named range for A1:A500 that never grows/shrinks
after cutting and pasting, etc.? I.e, I want the named range to always
refer to A1:A500.
 
Use indirect

define>name and use

=INDIRECT("Sheet1!A1:A500")

replace Sheet1 with the name of the sheet
 
insert>name>nameit "dontmove"
in the refers to box
=indirect("Sheet1!$a$1:$a$500")
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

specify an ending row in an array formula 3
How to refer a name in VBA 4
Counting specific word occurences in a cell 3
Name Issue 4
Creating independant Arrays of sequential numbers 1
Sequential numbers in a formula? 2
Automatic filter for unique records 10
Unique entries 4

Back
Top