Hi, got a convoluted one (at least by my low standards). Using Excel 2003.

I need to set up finance reports that total data using 3 criteria. To get
the data, I export a report from my finance system to a spreadsheet, which I
then copy into a blank (but pre-named) worksheet in the same workbook.

I then "tidy up" this spreadsheet with a simple macro to delete blank
rows/columns and add some columns with conditional arguments that helps sort
out different types of transaction types.

In the reports I'm using the following function:

=SUMPRODUCT(--('Report'!V2:V30000!="S"),--('Report'!M2:M30000!="<code"),('Report'!H2:H30000 !))

Whe
"S" is one of my added conditional arguments that identifies certain
transaction types;
<code is the finance project code I'm reporting on; and
H2:H30000 is the amounts I want to add up.

Now, this formula works just fine - provided the data's already in the
cells. Trouble is that I need these to stay just as they are when I then
remove the original data - remember, I need to copy this in AFTER this is all
set up.

What I'm trying to achieve is a workbook to produce task reports (for about
30 tasks) that requires only we paste the raw data into the blank template,
run my little macro and hey presto, everything else updates.

I've looked at INDIRECT, but it doesn't seem to like array references. I
can't seem to stop Excel automatically updating the reference any other way.
Any help would be awesome.

Hi Mahhrat,

You could try incorporating the OFFSET function, as in:
=SUMPRODUCT(--(OFFSET('Report'!$V$1,1,):OFFSET('Report'!$V$1,299 99,)="S"),--(OFFSET('Report'!$M$1,1,):OFFSET('Report'!$M$1,299 99,)="<code"),(OFFSET('Report'!$H$1,1,):OFFSET('R eport'!$H$1,29999,))

--
Cheers
macropod
[MVP - Microsoft Word]


"Mahhrat" wrote in message ...
Hi, got a convoluted one (at least by my low standards). Using Excel 2003.

I need to set up finance reports that total data using 3 criteria. To get
the data, I export a report from my finance system to a spreadsheet, which I
then copy into a blank (but pre-named) worksheet in the same workbook.

I then "tidy up" this spreadsheet with a simple macro to delete blank
rows/columns and add some columns with conditional arguments that helps sort
out different types of transaction types.

In the reports I'm using the following function:

=SUMPRODUCT(--('Report'!V2:V30000!="S"),--('Report'!M2:M30000!="<code"),('Report'!H2:H30000 !))

Whe
"S" is one of my added conditional arguments that identifies certain
transaction types;
<code is the finance project code I'm reporting on; and
H2:H30000 is the amounts I want to add up.

Now, this formula works just fine - provided the data's already in the
cells. Trouble is that I need these to stay just as they are when I then
remove the original data - remember, I need to copy this in AFTER this is all
set up.

What I'm trying to achieve is a workbook to produce task reports (for about
30 tasks) that requires only we paste the raw data into the blank template,
run my little macro and hey presto, everything else updates.

I've looked at INDIRECT, but it doesn't seem to like array references. I
can't seem to stop Excel automatically updating the reference any other way.
Any help would be awesome.