pivot table perhaps?

J

jacob farino

I have a spreadsheet, and it has gotten very long; i keep redoing the TOTALS
row, to be at the bottom, but it takes a long time to scroll down! Can you
make it so that it is always on the screen somewhere?

Also, i set up auto-filters so that I can view my totals by salesperson, but
it won't display that particular saleperson's subtotals when filtered. Is
there a simple way to show this?

Thanks so much
 
A

Andy B

Hi

A couple of suggestions.
Have your TOTALS in a row at the top of the page - which is the way most
people get round this problem.
You could use the SUBTOTAL() function for your sealepersons' totals. This
will only sum/count etc values that are showing after an AutoFilter.
 
J

jacob farino

Andy:
The totals bar at top will do just fine, thank-you for the suggestion!
(would the formula be =sum(B:B) ? I think that would return a loop if i
tried displaying the results in B1, so what would it be? =sum(B: infinity?)

Would you mind terribly, going into a little more detail on the SUBTOTAL
feature and how to set it up though? I'm afraid I'm not terribly familiar
with subtotals and how to get them to display after a filter.
Thanks again.
 
A

Andy B

Hi

For the total, if your data starts on row3, for example, use:
=SUM(A3:A65000)
If you look in Help for Subtotal Worksheet Function you'll find the options.
You'd probably use:
=SUBTOTAL(9,A3:A65000)
which will give you a SUM of all cells in the range that have not been
filtered out.
Hope this helps.
 
J

JE McGimpsey

If you can use any recent version of MacXL, or XL2003, the List Manager
does exactly what you're looking for. Start the list in row 2, and in
row 1, enter =<totals cell reference> (e.g., if the totals row of the
list was in row 30, enter =C30 in cell C1). The total will update as you
add or subtract data, and will reflect the filtered data only.
 
Top