Question about summarizing sorted data
 

Ok so heres my problem. I have a Workbook with a couple of sheets which are
generated by a query I run. The query takes all the data I have and lists
each department and the cost of an item they have taken from storage.

I then use a macro to sort everything by department and then sum the total
cost of items used by each department. I am making a summary sheet so that a
glace i can see the total cost of items used by each department.

All the information is on the previous sheet and is summed I just want to
take the info and put it on the summary sheet. The problem I am having is
each time I run the query( every week) the Total cost cells (for each
department) are in a different place because they use a different amounts of
items each week.

Is there a way excell can find these cells automatically and place them in
the correct place on my summary sheet ?

Please Advise
Thanks in advance
Terry

Question about summarizing sorted data
 

Just one more thing to add, when the data is sorted it creates a row that is
blank except for the Department Number and the total amount which are the
exact numbers i need to display on the summary sheet.
I figure this might be useful and an easy way to find which rows i need
pasted but I still am not sure what to do.

Terry

"Terry" wrote:

Ok so heres my problem. I have a Workbook with a couple of sheets which are
generated by a query I run. The query takes all the data I have and lists
each department and the cost of an item they have taken from storage.

I then use a macro to sort everything by department and then sum the total
cost of items used by each department. I am making a summary sheet so that a
glace i can see the total cost of items used by each department.

All the information is on the previous sheet and is summed I just want to
take the info and put it on the summary sheet. The problem I am having is
each time I run the query( every week) the Total cost cells (for each
department) are in a different place because they use a different amounts of
items each week.

Is there a way excell can find these cells automatically and place them in
the correct place on my summary sheet ?

Please Advise
Thanks in advance
Terry

Question about summarizing sorted data
 

could the be the vlookup function ?

"Terry" wrote:

Ok so heres my problem. I have a Workbook with a couple of sheets which are
generated by a query I run. The query takes all the data I have and lists
each department and the cost of an item they have taken from storage.

I then use a macro to sort everything by department and then sum the total
cost of items used by each department. I am making a summary sheet so that a
glace i can see the total cost of items used by each department.

All the information is on the previous sheet and is summed I just want to
take the info and put it on the summary sheet. The problem I am having is
each time I run the query( every week) the Total cost cells (for each
department) are in a different place because they use a different amounts of
items each week.

Is there a way excell can find these cells automatically and place them in
the correct place on my summary sheet ?

Please Advise
Thanks in advance
Terry

Question about summarizing sorted data
 

Hi Terry,

try to use sumproduct function, assuming that you have few columns on the
sheets and the department code is on column C and the value on D Column,

try
=sumproduct(--(c2:c1000="production");(c2:c1000))


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Terry" escreveu:

could the be the vlookup function ?

"Terry" wrote:

Ok so heres my problem. I have a Workbook with a couple of sheets which are
generated by a query I run. The query takes all the data I have and lists
each department and the cost of an item they have taken from storage.

I then use a macro to sort everything by department and then sum the total
cost of items used by each department. I am making a summary sheet so that a
glace i can see the total cost of items used by each department.

All the information is on the previous sheet and is summed I just want to
take the info and put it on the summary sheet. The problem I am having is
each time I run the query( every week) the Total cost cells (for each
department) are in a different place because they use a different amounts of
items each week.

Is there a way excell can find these cells automatically and place them in
the correct place on my summary sheet ?

Please Advise
Thanks in advance
Terry