As per title, I would like to shift cells down automatically. I want to do this because I don't know how many entries I will need to put in to the workbook. I hope im clear enough.

Hi iffy,

So how is Excel supposed to know:
a) which cells to move; and
b) how far to move them?
Also, what happens to any formulae that reference either:
a) the cells you move; or
b) the range above the cells you move?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

One other way of explaining it is - I don't know how many entries I will put in to the worksheet but at the same time I need a formula at the bottom of the sheet that will keep a running total of some simple values that the entries provide.

I think it is absolute cell referincing that tracks and updates data change/position change in worksheet

I often put my totals at the very top of the sheet - above the column headers. That way, I always know where they are (don't have to scroll around looking for them) and I don't have to insert extra rows between the totals and the data when I want to add more data.

If you want to have your formulae at the bootom of the range, the safest way is to have a 'boundary' row at each end of the range that has no values in it (eg just a string of '-' characters) and have your formulae include those 'boundary' rows. As you insert/delete rows, the formulae will automatically adjust, based on the new addresses of the boundary rows.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I'm interested in this method, do you have a visual example to show?

However I'm sure what I'm asking for is actually simple (remember being taught at college).

Hi Iffy,

I just noticed you are using Excel 2003. A new feature called "Lists" was introduced in Excel 2003 which you can use here. Basically, they're a fancied-up version of the tip Macropod gave in his last post. Here's a quick online course on how to use them:
http://office.microsoft.com/en-us/tr...001080135.aspx

Let us know if using a list solves your question?


As I said, I do like to keep my totals at the top of the sheet - it particularly suits the type of work I do in Excel. This is just my preference and is not by any means a golden rule.

Hope that helps...
Colin

I am also interested in the answer to this question. I cannot have the amount listed at the top of my table it must show at the bottom. I have a row under my pivot table that contains the following formula: "=GETPIVOTDATA("SERVICE COST",$A$13). I want this row containing this formula to move down every time data is entered on my table and updated on my pivot where this row is located. I would always want the row to move 5 rows below any new information that comes in from my table. Can this be done? if so how would i do it?

Thank you,
Ruth