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

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" <Manuel@discussions.microsoft.com> wrote in message 
news:6D512FDE-9353-43F2-8C87-811BC639DF40@microsoft.com...
> 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
> 

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" <Manuel@discussions.microsoft.com> wrote in message 
> news:6D512FDE-9353-43F2-8C87-811BC639DF40@microsoft.com...
> > 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
> > 
> 
> .
> 

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