Keep Same Range

[jlclyde]

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

 

[Max]

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]

Use $A$1:$A$1000





- Show quoted text -

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

 

[jlclyde]

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

 

[RonaldoOneNil]

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.