| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
