Sum using ordinal/relative positioning
 

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

Sum using ordinal/relative positioning
 

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

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

--
Rick (MVP - Excel)


"Manuel" wrote in message
...
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

Sum using ordinal/relative positioning
 

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.

"Rick Rothstein" wrote:

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

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

--
Rick (MVP - Excel)


"Manuel" wrote in message
...
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


.

Sum using ordinal/relative positioning
 

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