#1
|
|||
|
|||
How to make cells shift down automatically
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.
|
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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.
|
#5
|
||||
|
||||
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] |
#6
|
|||
|
|||
Quote:
However I'm sure what I'm asking for is actually simple (remember being taught at college). |
#7
|
||||
|
||||
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 |
#8
|
|||
|
|||
How to make cells shift down automatically
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can you make an EDITABLE text box that will automatically duplicate on each page in W | dha3 | Word | 0 | 08-05-2010 07:17 PM |
How can I change the colors of cells automatically based on Job Completion? | Learner7 | Excel | 0 | 07-06-2010 10:47 PM |
Count range cells eliminating merge cells | danbenedek | Excel | 0 | 06-15-2010 12:40 AM |
format cells to automatically place quotes around text | dirtleg | Excel | 1 | 09-16-2008 01:52 PM |
Automatically entering/fill data in cells in Excel 2003 | dipdog | Excel | 0 | 08-17-2006 08:37 AM |