Help in creating a formula

[Lazurus for excel]

Hi,

Here is my scenario, I am attempting to export data from another program
into excel (financial data) that changes daily making my cell location for
totals change is there a formula that can combat that??? to eliminate data
entry time!!! :P

[Arvi Laanemets]

Hi

Place your totals into table header, and calculate them using dynamic
ranges. An example:

Your data are on sheet MyData in range A5:E5 and down. In range A4:E4 are
column headers. Rows 1:3 are empty. Column A contains some identifier (Name
or Date etc.) and is never is empty, whenever there are any data in row.
Columns C:E contain numeric data, you need to sum.

Into B2 you enter text "Total:"
Define named ranges like
Range1=OFFSET(MyData!$C$5,COUNTIF(MyData!$A:$A,"<>")-1,1)
Range2=OFFSET(MyData!$D$5,COUNTIF(MyData!$A:$A,"<>")-1,1)
Range3=OFFSET(MyData!$E$5,COUNTIF(MyData!$A:$A,"<>")-1,1)

Into C2 enter the formula:
=SUM(Range1)
D2=SUM(Range2)
E2=SUM(Range3)

You can improve it, replacing the SUM function with SUBTOTAL, like:
C2=SUBTOTAL(9,Range1)
Now, when you filter some data using autofilter feature, totals for filtered
rows is displayed at header.

As bonus, you can set header, along with totals, to be always visible, using
Freeze Panes feature


Arvi Laanemets


"Lazurus for excel" <Lazurus for excel@discussions.microsoft.com> wrote in
message news:4CFBE194-2436-4DAF-8ABD-FCD02C1400A0@microsoft.com...
> Hi,
>
> Here is my scenario, I am attempting to export data from another program
> into excel (financial data) that changes daily making my cell location for
> totals change is there a formula that can combat that??? to eliminate data
> entry time!!! :P