I might be re-inventing the wheel here, but any assistance would be
appreciated.

I have a data import (SQL query) to one sheet of sales data (date, rep name,
amount, qty etc. etc. )
I am using a second sheet to extract certain info such as Sales in the week,
month, to date etc.
Trouble is, the data coming in does not multiply the QTY with the Sales unit
price, so I need an extra column
for total sales. I cannot put an extra column in the data sheet manually due
to the refresh moving the cell refences out of line
So how do I tell excel to add this column of data when I refresh the data ?

BTW, the formulas to extract the required data in the reporting sheet are
long enough and Im finding too difficult to add an extra qty * sales in them
for example...
=COUNT(IF((data!FU$2:FU$6382="MAREK")*(MONTH(data! M$2:M$6381)=$F$1)*(YEAR(data!M$2:M$6359)=$F$3),dat a!E$2:E$6381))
its just getting too mad!!

Thanks in advance
--
Vass

Vass,
Can you not edit the SQL that pulls the data from the DB to include a
calculated field of Qty*Sales AS TotalSales ?
Or am I missing the point ?

NickHK

"Vass" wrote in message
. uk...
I might be re-inventing the wheel here, but any assistance would be
appreciated.

I have a data import (SQL query) to one sheet of sales data (date, rep
name,
amount, qty etc. etc. )
I am using a second sheet to extract certain info such as Sales in the
week,
month, to date etc.
Trouble is, the data coming in does not multiply the QTY with the Sales
unit
price, so I need an extra column
for total sales. I cannot put an extra column in the data sheet manually
due
to the refresh moving the cell refences out of line
So how do I tell excel to add this column of data when I refresh the data
?

BTW, the formulas to extract the required data in the reporting sheet are
long enough and Im finding too difficult to add an extra qty * sales in
them
for example...

=COUNT(IF((data!FU$2:FU$6382="MAREK")*(MONTH(data! M$2:M$6381)=$F$1)*(YEAR(da
ta!M$2:M$6359)=$F$3),data!E$2:E$6381))
its just getting too mad!!

Thanks in advance
--
Vass

"NickHK" wrote in message
...
Vass,
Can you not edit the SQL that pulls the data from the DB to include a
calculated field of Qty*Sales AS TotalSales ?
Or am I missing the point ?


I could if I knew how
the SQL already removes duplicates and some rows that are blank
with this:
Select * From itran INNER JOIN ihead ON itran.it_doc = ihead.ih_doc WHERE
itran.it_status = 'A' AND ihead.ih_quotat = "" AND itran.it_stock <""

Can you offer an addition to it?
thanks
--
Vass