Moving a row up or down whilst keeping the equation the same.

del.frost wrote:

trying to ascertain whether it's possible to write a formula that looks to the "cell above" as a source figure but doesn't follow that cell to where it now is, if it's beed dragged.

Yes, that's possible. In your Total columns you could use the OFFSET function, like this:

In G5, filled down the column:

=OFFSET(G5,−1,0,1,1)+E5−F5

But, since your screenshot suggests you are working with quite a large table, you may find that using OFFSET in so many cells makes your document sluggish. If that happens, it would be because OFFSET is a so-called "volatile" function that recalculates every time you make a change to the document.

SG

del.frost wrote:

"take the sum of the previous row's TOTAL, add anything in the IN column, subtract anything in the OUT & give me a new total in this row's TOTAL column".

Unfortunately, each cell reference is cell specific

This doesn't eliminate the problem entirely, but it does make it easier to "adjust" formulas after you drag a row up or down. Use a formula with "expanding ranges" rather than specific cell references, as in this simplified example:

The formula in D3, filled down:

=SUM(B$2:B3)−SUM(C$2:C3)

The formula in G3, filled down:

=SUM(E$2:E3)−SUM(F$2:F3)

Then, after you drag rows into a new position, just fill the formulas down again from row 3. That's easier than adjusting each formula in each row, one by one.

SG

INDIRECT(ADDRESS()) is another way to refer to a cell.

If B is "In" and C is "Out" and D is "Total", a formula for column D that would do what you are asking is

=INDIRECT(ADDRESS(ROW()−1,COLUMN()))+B−C

Except for the first row of column D which would be

=B-C (or simply a number you enter as the beginning balance)

and should not be moved.

I don't know if this is computationally faster or slower than OFFSET on larger tables or if all those ROW and COLUMN functions are slow to get recalculated.

Hi del,

I don't think that dragging rows is the way to go. Leave your personal banking form (table) intact.

Try using the SUMIF or SUMIFS functions in other tables.

Then you can calculate the bank balance for each account.

The Numbers approach is to "pull" data from a large table into small tables, each table having a purpose.

A screen shot (hide personal data) of your personal banking form will help us to see what you see.

Click on the table to make it active. Then it will show row and column labels. Please include those labels in the screenshot.

The more information you give, the easier it will be for fellow users to find a solution 😉.

Regards,

Ian.

Hi del,

what I need is a single spreadsheet showing each of my accounts side by side with all transactions in chronological order, including all past transactions for the year & projected cashflow until the end of the year.

OK, lets go with a single table. SG's solution of "expanding ranges" is excellent and I agree with that approach.

I am still trying to visualise a single table of several accounts, each in chronological order. Does each bank report arrive on the same day? Are all accounts included in a single table? Do you edit that table to match SG's example?

I do not understand the need to drag rows up or down. That drags whole rows.

Numbers mimics a database. In a single table, each row is a record; each column is a field.

Please help us to help you with a screenshot (anonymous data).

Regards,

Ian.

Hi Del,

Try this on a COPY of your table:

Instead of dragging a row up or down, Insert a new row in the correct location, Copy the entered data from the misplaced row, Paste that data into the new row, then delete the misplaced row.

Depending how your formulas are constructed, Numbers should automatically adjust formulas to handle the insertion or deletion of these rows, provided you copy and paste only the data that has been entered.

Regards,

Barry

del.frost wrote:

when I delete the row, every cell onwards from the deleted row would contain a warning triangle because it's lost its source cell.

That is the same behavior you would observe in Excel too, a #REF! error.

SG

del.frost wrote:

am I right in thinking that the process described above wouldn’t ever work, or is it still a case of incorrect formulae?

Have you tried the formula I suggested above that uses OFFSET?

SG

Glad to hear the formula with OFFSET worked and isn't causing your document to be become sluggish.

This is an interesting case because it is one of the few functions (perhaps the only function?) that can refer to the same cell that the formula is in.

Thanks for the green tick!

SG