Query rows and generate a report: possible with Excel?

**ddrislane** · 03-12-2008, 04:19 PM

I am part of a non-profit gallery where inventory and sales of art are captured in Excel 2003 (soon 2007). I wish to query all rows looking for a certaiun value (say, sales amt >0) then sort all rows by artist name and then generate separate sales reports for each artis so we can pay them commission.

In the attached example, I want to select by each artist and list their art sales on separate reports (hopfully separate worksheets). Hee is a basic example (also contained in teh attachment):

artist art piece price paid commission
tom a 33 19.8
tom e 44 26.4
dave d 55 33
bill f 6 3.6
tom c 67 40.2
dave t 88 52.8
jim q 0 0
bill s 22 13.2

And here's what I would like (on separated worksheets or the equivalent:

artist art piece price paid commission
tom a 33 19.8
tom e 44 26.4
tom c 67 40.2
TOTAL COMMISSION 86.4

artist art piece price paid commission
dave d 55 33
dave t 88 52.8
TOTAL COMMISSION 85.8

artist art piece price paid commission
bill f 6 3.6
bill s 22 13.2
TOTAL COMMISSION 16.8

artist art piece price paid commission
jim q 0 0
TOTAL COMMISSION 0

Yes, yes, I know: USE A PIVOT TABLE! But this approach is too complicated for our users who are mostly Mac users and very casual Excel users.

From experience, I know this would be a pretty easy job in Access but I don't want to go down that path.

Any ideas? Thanks!