Sum using ordinal/relative positioning

[Manuel]

Hi,

I'm not sure I'm using the correct terminology but here's what I'm trying to
accomplish.

I have an Excel Sheet that is linked to an Access table (I did this by
selecting Data / Import External Data / Import Data...). This works fine.
Let's say there are 300 records in the table not including the header row. I
have formulas in the first empty row in the spreadsheet (row 302, in this
example), that simply subtract the value in the last two rows (so,
=A300-A301, in this example). My table is dynamic and records can be added
or deleted. When records are added and the data in the Excel Sheet is
updated/refreshed the formulas do not retain their ordinal or relative
positions. I.e., rather than subtracting the value in the last two rows they
now subtract the value in what was formally the last row and the value in the
"new" last row. So, in this example, =A301-A304, if three records are added
to the table. I want the formula to reposition to =A303-A304.

How do I get Excel to reference the last two rows regardless of whether rows
are inserted or deleted, i.e. the two rows above where the formula is placed?

Your assistance is greatly appreciated!

Thanks,
Manuel

 

[Rick Rothstein]

Try this formula in A302 and copy it across as needed...

=INDEX(A1:A300,ROW(A300))-INDEX(A1:A300,ROW(A301))

 

[Manuel]

Thank you for the suggestion but unfortunately it did not work. The formula
references are still not adjusting to the last two rows. Any other
suggestions? I suspect that this can be done with VBA.

 

[Herbert Seidenberg]

Excel 2007
With dynamic Tables
http://c0444202.cdn.cloudfiles.rackspacecloud.com/11_21_09.xlsx