Keep Same Range
 

I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay

Keep Same Range
 

Use $A$1:$A$1000

"jlclyde" wrote:

I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay

Keep Same Range
 

You could use INDIRECT, eg:
=SUM(INDIRECT("'Sheet1'!A1:A1000"))
will always point to A1:A1000 in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"jlclyde" wrote in message
...
I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay

Keep Same Range
 

On Dec 30, 8:06*am, RonaldoOneNil
wrote:
Use $A$1:$A$1000



"jlclyde" wrote:
I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. *Is this possible? *I
thought I read somethign someplace. *Any help is appreciated.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

This does nto work if the range has a row removed or deleted. It
changes the reference to $A1:$A999.
Jay

Keep Same Range
 

I think you'll find that deletion of rows will cause that formula to change,
Ronaldo.

If you want it not to change, replace reference to A1:A1000 by
INDIRECT("A1:A1000")
--
David Biddulph

"RonaldoOneNil" wrote in message
...
Use $A$1:$A$1000

"jlclyde" wrote:

I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay

Keep Same Range
 

Thank you dave and Max. The indirect works great. I forgot to add
the quotes. So it was Sumproduct((INDIRECT(A1:A1000)*.... instead
of ...INDIRECT("A1:A1000")....
Thanks again,
Jay

Keep Same Range
 

So it does, I thought that using a dollar sign in front of the number as well
as the letter would make it an absolute reference that does not change.

"David Biddulph" wrote:

I think you'll find that deletion of rows will cause that formula to change,
Ronaldo.

If you want it not to change, replace reference to A1:A1000 by
INDIRECT("A1:A1000")
--
David Biddulph

"RonaldoOneNil" wrote in message
...
Use $A$1:$A$1000

"jlclyde" wrote:

I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay

Keep Same Range
 

The absolute reference stops it changing when you copy it up or down or
across, but references still point to the same cell (not the same reference)
when rows or columns are inserted or deleted. That's why you need INDIRECT
for situations like this.
--
David Biddulph

"RonaldoOneNil" wrote in message
...
So it does, I thought that using a dollar sign in front of the number as
well
as the letter would make it an absolute reference that does not change.

"David Biddulph" wrote:

I think you'll find that deletion of rows will cause that formula to
change,
Ronaldo.

If you want it not to change, replace reference to A1:A1000 by
INDIRECT("A1:A1000")
--
David Biddulph

"RonaldoOneNil" wrote in
message
...
Use $A$1:$A$1000

"jlclyde" wrote:

I have a formula and the range in the formula is A1:A1000 and I want
this to stay A1:A1000 even if I delte rows. Is this possible? I
thought I read somethign someplace. Any help is appreciated.
Thanks,
Jay