Keeping a range constant when inserting rows

Hello,

I'm trying to keep a range of cells constant within a 
function when I insert a row (e.g. average(a1:a6) becomes 
average(a1:a7) but I want it to keep the a1:a6 range).  
Even if I use absolute cell references ($a$1:$a$6), it 
doesn't help.

I would greatly appreciate any ideas.

Thanks,
Jeff
0
jcolso (2)
8/27/2003 2:47:44 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1238 Views

Similar Articles

[PageSpeed] 50

Jeff,

In your formula, use:

=AVERAGE(INDIRECT("A1:A6"))

Absolute cell references (dollar signs) do one thing only:  They keep any
copies you make of the cell references from changing relatively as they're
copied.  They still change when the cells to which they refer are moved (as
with an insert or delete).

--
Regards from Virginia Beach,

EarlK
-------------------------------------------------------------

"Jeff Olson" <jcolso@hotmail.com> wrote in message
news:0dff01c36caa$2cd87810$a401280a@phx.gbl...
> Hello,
>
> I'm trying to keep a range of cells constant within a
> function when I insert a row (e.g. average(a1:a6) becomes
> average(a1:a7) but I want it to keep the a1:a6 range).
> Even if I use absolute cell references ($a$1:$a$6), it
> doesn't help.
>
> I would greatly appreciate any ideas.
>
> Thanks,
> Jeff


0
EarlK
8/27/2003 3:36:23 PM
EarlK,

Thanks so much - exactly what I wanted!

Jeff

>-----Original Message-----
>Jeff,
>
>In your formula, use:
>
>=AVERAGE(INDIRECT("A1:A6"))
>
>Absolute cell references (dollar signs) do one thing 
only:  They keep any
>copies you make of the cell references from changing 
relatively as they're
>copied.  They still change when the cells to which they 
refer are moved (as
>with an insert or delete).
>
>--
>Regards from Virginia Beach,
>
>EarlK
>----------------------------------------------------------
---
>
>"Jeff Olson" <jcolso@hotmail.com> wrote in message
>news:0dff01c36caa$2cd87810$a401280a@phx.gbl...
>> Hello,
>>
>> I'm trying to keep a range of cells constant within a
>> function when I insert a row (e.g. average(a1:a6) 
becomes
>> average(a1:a7) but I want it to keep the a1:a6 range).
>> Even if I use absolute cell references ($a$1:$a$6), it
>> doesn't help.
>>
>> I would greatly appreciate any ideas.
>>
>> Thanks,
>> Jeff
>
>
>.
>
0
jcolso (2)
8/27/2003 6:05:52 PM
I am trying to do the same only using about 2,000 rows. Is 
there a way to use this and not have to manually put in 
each cell. In other words, I am using  =VALUE(A1) in cell 
A2.  And I want to do that from A1 through A2000 but using 
your formula below, it won't allow the copy function with 
changing A1 to A2 and so forth. 
>-----Original Message-----
>Jeff,
>
>In your formula, use:
>
>=AVERAGE(INDIRECT("A1:A6"))
>
>Absolute cell references (dollar signs) do one thing 
only:  They keep any
>copies you make of the cell references from changing 
relatively as they're
>copied.  They still change when the cells to which they 
refer are moved (as
>with an insert or delete).
>
>--
>Regards from Virginia Beach,
>
>EarlK
>----------------------------------------------------------
---
>
>"Jeff Olson" <jcolso@hotmail.com> wrote in message
>news:0dff01c36caa$2cd87810$a401280a@phx.gbl...
>> Hello,
>>
>> I'm trying to keep a range of cells constant within a
>> function when I insert a row (e.g. average(a1:a6) 
becomes
>> average(a1:a7) but I want it to keep the a1:a6 range).
>> Even if I use absolute cell references ($a$1:$a$6), it
>> doesn't help.
>>
>> I would greatly appreciate any ideas.
>>
>> Thanks,
>> Jeff
>
>
>.
>
0
cnjs (1)
8/27/2003 8:07:49 PM
So you want to end up with 2000 formulas like:

	=value(indirect("a157"))

How about a couple of steps.

first put this formula in your cell

	="$$$VALUE(INDIRECT(""A"&ROW(A1)&"""))"
Drag it down 2000 rows.

You'll end up with a cells that evaluate to this:

$$$VALUE(INDIRECT("A1"))
$$$VALUE(INDIRECT("A2"))
$$$VALUE(INDIRECT("A3"))
$$$VALUE(INDIRECT("A4"))

Now select that range of 2000 cells.
Edit|copy
Edit|paste special|Values

Now Edit|replace
$$$ 
with 
= (equal sign)

You've got 2000 formulas.

An aside:  Do you really need the =Value() portion?

cecil wrote:
> 
> I am trying to do the same only using about 2,000 rows. Is
> there a way to use this and not have to manually put in
> each cell. In other words, I am using  =VALUE(A1) in cell
> A2.  And I want to do that from A1 through A2000 but using
> your formula below, it won't allow the copy function with
> changing A1 to A2 and so forth.
> >-----Original Message-----
> >Jeff,
> >
> >In your formula, use:
> >
> >=AVERAGE(INDIRECT("A1:A6"))
> >
> >Absolute cell references (dollar signs) do one thing
> only:  They keep any
> >copies you make of the cell references from changing
> relatively as they're
> >copied.  They still change when the cells to which they
> refer are moved (as
> >with an insert or delete).
> >
> >--
> >Regards from Virginia Beach,
> >
> >EarlK
> >----------------------------------------------------------
> ---
> >
> >"Jeff Olson" <jcolso@hotmail.com> wrote in message
> >news:0dff01c36caa$2cd87810$a401280a@phx.gbl...
> >> Hello,
> >>
> >> I'm trying to keep a range of cells constant within a
> >> function when I insert a row (e.g. average(a1:a6)
> becomes
> >> average(a1:a7) but I want it to keep the a1:a6 range).
> >> Even if I use absolute cell references ($a$1:$a$6), it
> >> doesn't help.
> >>
> >> I would greatly appreciate any ideas.
> >>
> >> Thanks,
> >> Jeff
> >
> >
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/27/2003 11:49:58 PM
Reply:

Similar Artilces:

Keep black parts black??
In 2002 I used this feature for a quick duotone. Colorize the photo, leaving black parts black and it worked great. Now in 2003, this feature is grayed out in spot color mode. What's up?? Thanks! Greg ...

make colour in rows stay
How can I lock the fill colours in a row even when I change around the data or sort alphabetically? Thanks and God Bless, Harley Use CF http://www.cpearson.com/excel/banding.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Crowraine" <Crowraine@discussions.microsoft.com> wrote in message news:ED158D7D-62AB-4DA8-A948-2A6556D8047C@microsoft.com... > How can I lock the fill colours in a row even when I change around the data > or sort alphabetically? > > Thanks and God Bless, > Harley ...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

Word 2008 keeps crashing
I recently purchased a Macbook Pro and installed Office 2008. Whenever I type in Word or copy/paste from another document, I either get an error that says "Insufficient memory" or the application freezes and will not respond. Is there something I can do or just have to wait for an update? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285228210_9363962 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 10:37 AM, in article ee8c846.-1@web...

exchange 5.5 will not keep new server name.
We have exchange 5.5 in enviroment where windows server 2003 is setup along with active directory and active direcitory intergrated zone which is replicated to other servers. Problem: I enter the Exchange Server Name followed by the mailbox name then I select the button [CHECK NAME] In the exchange server name field after entering the new exchange server name the old exchange server name is displayed instead. This only happens in office 2003 professional and not in office 2000 professional "old exchange server name" How did you go about changing the name of the server...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

how to insert borders on flyer within Microsoft publisher?
Please help, I'm trying to insert a border around a flyer...I'm using Microsoft Publisher. Thanks, Harriet Is it a clipart border, Borderart or a simple rectangle? What problems are you having? What version Publisher? Any border you insert should be sent to the back so it does not interfere with your main design. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Harriet" <Harriet@discussions.microsoft.com> wrote in message news:7E2ED4D8-CC09-497F-A17C-44AE41F951C9@microsoft.com... > Please help, I'm ...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

how do I keep email messages online and also go to outlook
When I open outlook my email messages go to outlook and if I am away from home can no longer open my yahoo mail account or att mail acount and see my messages because then downloaded into my outlook. Is there a way to have the move ot outlook, but not leave my online account? Set Outlook to leave messages on the server for the accounts in question. -- Russ Valentine "jann" <jann@discussions.microsoft.com> wrote in message news:146B7F22-72FC-4BF6-A672-06EC5ED88C87@microsoft.com... > When I open outlook my email messages go to outlook and if I am away from &...

underline in row not repeating
It seems as if my bottom of the cell outline is not repeating on subsequent pages. I have a few header rows on a sheet. The bottom row has a bottom line. I'm talking about cell outlines, not underlining of words in the cell. Anyway, I've told the print option to repeat the first 3 rows on following pages but the bottom outline does not seem to repeat. How can I fix this? Thanks, Keith Never mind. This seems to be working. Not sure what was going on the first time. "Keith G Hicks" <krh@comcast.net> wrote in message news:OM82rj8rIHA.2068@TK2MSFTNGP05.phx.gbl... >...

How to unhide Excel rows after they are automatily hidden
Helpful advice on posting to newsgroups here... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Chuck" wrote in message ...

Altering the range that is plotted by a chart via VBA
Good morning all. I haven't really done a huge amount on charting, so I'm sure you'll think that these questions are a bit simplistic, but here goes! I plot a chart from a worksheet that contains three columns of data - the X axis contains week numbers, and is in B29:B54. Actual hours are entered in F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the form: =SUM($F$29:F29) So, each week, in order to plot another week's data, I have to copy this cumulative formula down by one cell, which, considering I have a lot of these reports, is a bit time cons...

Equation Numbers, inserting field
I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc. When I get to equation 27 it needs to be AA, then 28 will be AB...) next to. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs. I would like to have both the equation reference and the text paragraphs to update if I add an equation to the middle of the document. I know I should use a field code and cross reference it in the tex...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

Want to modify this procedure to have a range of periods rather than a single period
This is a multi-part message in MIME format. ------=_NextPart_000_00BF_01CB206B.89E3AC60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the procedure below I want simply modify the periods procedure below to be -- perpost >=3D = '201001' and <=3D '201012' so I want a range of periods rather than a = single period as in the example below I would really appreciate it if someone can help me out on this. Thanks=20 Sammy USE DEMOAPP Declare @perpost nvarchar(8) /* Do not edit Above=20 ...

How do I keep Outlook from downloading messages with attachments?
I use Outlook with dial-up and I'm trying to generate a rule that notifies me of large (>1MB) attachments before it begins downloading them. However, all rules by default seem to apply "after message arrives," i.e. when it's too late. Any suggestions? OL rules apply after download. Use web access for your ISP to view mail, or mailwasher eg Whether your ISP has any rules/options may be an option. "DRRALF" <DRRALF@discussions.microsoft.com> wrote in message news:40BA86BD-A57B-462B-894A-F460B4F15727@microsoft.com... > I use Outlook with dial-up and I&#...

Help with Highlighting all duplicates in a row
I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format->Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE) Where A5 is the first cell in Range1....

Why can't I unhide rows 1 to 47 in a worksheet?
No matter what I try, I can't show the rows I hid. Please help. Hi, HTH Press ctrl+home. Hold down the shift key and click in A48 Press ctrl+shift+9 ray "lcarr13" wrote: > No matter what I try, I can't show the rows I hid. Please help. Because you have freeze panes. Window|Unfreeze panes and scroll up and there you go. lcarr13 wrote: > > No matter what I try, I can't show the rows I hid. Please help. -- Dave Peterson "lcarr13" <lcarr13@discussions.microsoft.com> wrote in message news:9534BD16-F9BD-486F-9588-0DE35BB351AC@microso...

Deleting every-other row in a spread sheet
I have a spreadsheet in excell that contains info imported from word. As a result every-other row is blank. I would like to erase these empty rows so if anyone knows how to go about this it would be gratefully appreciated!! Thanks in advance Steve Smintey, If you a familiar with VBA, you can delete the rows via code. For example, this procedure deletes rows with empty cells in column A: Sub DeleteRows() Dim lLastRow As Long Dim Rng As Range Application.ScreenUpdating = False Rows(1).Insert Range("A1").Value = "Temp" With ActiveSheet ....

Return address of first item in a range
I'm sure there must be a simple solution for this I cannot think of. I am looking for a formula that will return the value of the first item in an input range. For example, I want to put a formula in cell A10 that will return the first item that is input in the range Range A1:A9 (not nesessarily the first cell). If A4=200, A7=150 and all other cells are empty, the formula should return 200 (the first item in the column). Thanks for your assistance. Ashley Ashley, One way is to use the array formula (entered with Ctrl-Shift-Enter): =INDIRECT("A"&MIN(IF(A1:A9<...

how do i insert a comment in an endnote?
Been trying to do this for ages for an assignment I have. I can not work out why I don't have the option of inserting a comment in my endnote? On Tue, 2 Mar 2010 20:59:01 -0800, kympossible <kympossible@discussions.microsoft.com> wrote: > Been trying to do this for ages for an assignment I have. I can not = work out=20 > why I don't have the option of inserting a comment in my endnote? Comments are not allowed in endnotes or footnotes. Neither are breaks, page numbers, diagrams, or text boxes. I'm guessing here but it probably has to do with the way that ...

Update cell based on date range
Hey guys! I was wondering if I could get some help here. I would lik to update a cell based on a date range. For example, I would like t update the value of a cell to the value of another cell if the curren date is between July 1st and July 10th. However, if the date i outside the date range, I want the value for that cell to not b updated, and be the previous value. Can anyone give me an example a to how I would do this? Thanks!! -- deversol ----------------------------------------------------------------------- deversole's Profile: http://www.excelforum.com/member.php?action=geti...