topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 1:46 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: any excel pivot table experts out there?  (Read 7330 times)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
any excel pivot table experts out there?
« on: September 13, 2007, 04:03 PM »
I have a excel sheet that shows individual basketball players that gets updated daily to show win/loss record of that day, and the total win/loss record overall.  I'd like the table to automatically get sorted every day so that the person with the best winning percentage is on top.  How can I do this with pivot tables?  Whenever I try to make a pivot table, I can't get it do to this kind of automatic sorting.  I don't even know if this is possible with pivot tables or something else.  Thanks.  Here's a screenshot of the excel sheet.
Screenshot - 9_13_2007 , 12_00_08 PM.pngany excel pivot table experts out there?

AbteriX

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 1,149
    • View Profile
    • Donate to Member
Re: any excel pivot table experts out there?
« Reply #1 on: September 13, 2007, 04:25 PM »
Don't know the syntax correctly out of mind
but you can script Excel with VBA (Visual Basic for Application)

You can look for an script with an
"OnLoad"-action like:

With Worksheet
If Target <> "" Then
x1 = Selection.Row
y1 = Selection.Column
Columns("A:Z").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells(x1, y1).Select
End If
End With



Sorry, just an hint.

---
Edit:
I have found some help and better code example with google:
http://pubs.logicale...PMArticle.asp?ID=302
http://www.anthony-v...om/vba/vbabasic2.htm

greeds
Stefan
« Last Edit: September 13, 2007, 04:50 PM by AbteriX »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: any excel pivot table experts out there?
« Reply #2 on: September 13, 2007, 04:37 PM »
ooo...I try to avoid VBA whenever possible.  It's always a big headache for me.

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: any excel pivot table experts out there?
« Reply #3 on: September 13, 2007, 05:48 PM »
if you're building pivots manually, you can set the sort order and field in the layout options dialog (apologies if this isn't exactly correct - it's not something I use very often - but it'll get you started)

double click on the field header, then select advanced.

Set the sort order (descending?) and the field name (it will probably default to the 'active' column anyway).

you can force your pivot to refresh on open under the options dialog (or select TABLE OPTIONS from the pivot context menu) - last check box is refresh on open

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: any excel pivot table experts out there?
« Reply #4 on: September 13, 2007, 06:20 PM »
Actually guys, I decided that this is something better suited for Access.  I'm going to try to create it in there.  Ugh...Access...so complex.

Curt

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 7,566
    • View Profile
    • Donate to Member
Re: any excel pivot table experts out there?
« Reply #5 on: March 23, 2010, 06:27 AM »
The Ultimate Excel Cheatsheet

http://www.investint...les/excelcheatsheet/

... we’ve put together the cream of the crop of Excel shortcuts in easy to use cheatsheets you can print up and keep handy.

Highlighting the most commonly used and commonly looked for Excel commands, this list puts it all at your fingertips.

Keyboard Shortcuts

Learn how to get around Excel using only the keyboard. These cheatsheets have the keyboard shortcuts for mainpulating files, editing content, changing formatting and navigating around workbooks.

...
http://www.investint...les/excelcheatsheet/

just F.Y.I.
« Last Edit: March 23, 2010, 06:28 AM by Curt »