I am using Excel 2007 and am trying to introduce a column into the .csv log showing the total points from each activator.
I have tried using the Logical fomula using ‘IF’ but this seems longwinded and would require an entry for each callsign. I require an automatic formula for say column ‘J’ which gives a running total as the call is entered. Suspect I may require to use the ‘Conditional Formula’ but when they start talking about ‘Pivot Tables’ etc, my brain seems to switch off!!
Any database expert here with a possible solution please?
In reply to G4CMQ:
David, actually the pivot table is really your best bet. Click the cell say K2 or somewhere where you want the results, click the insert tab on the top, then click pivot table in the ribbon on the left side. When the select table or range box pops up drag the mouse across the G,H & I columns from G1 to the bottom end of your I column so that all the relevant data in those 3 columns is selected. The click OK in the pop up box. Another box (the pivot table field list) will pop up on the right of the screen with three check boxes for Station Worked, Notes, and Points. Check the Station Worked & Points boxes, then click the X to close the pivot table field list box and there you have it.
As you add more data i.e. rows of new QSOs. Just click anywhere in the pivot table and options will pop up in the header ribbon with a button “change data source”. Then and you just reselect the relevant data with the new rows in columns G, H & I again.
Hope this makes sense to you.
73 jim g0cqk
Hi Jim
Most helpful…thanks a lot. However, I am having a ‘thick’ afternoon.
All this gives me is a list of the type of points for each call. For example. if we take DF2GN/P, it shows that I have worked him for 2, 4 ,6, 8 and 10 points summits, but not he total for his call.
Need that little last kick to the way to summate for the call.
In reply to G4CMQ:
Oh, how strange. When I do this as I described I end up with a two column pivot table with all the individual call signs listed in alpha sorted order in the first column and in the second column which is headed sum of points I get the total number of QSO points with each activator. I just did it with your data downloaded from the database and it works just great. If your email address is correct in QRZ, I’ll email you a copy.
73 jim
In reply to G0CQK:
Hi Jim
Your help much appreciated. Somewhere I located a ‘Sum of Points’ as distinct from ‘Points’ and I now have the totals for each activator. If I save this on my hard drive, the next time I save the new log to this file, I presume it will update or do I have to set up a new Pivot Table each time?
BTW, email in QRZ is correct but I think we are nearly there. I am sure others will want to do this.
In reply to G4CMQ:
David,
It really is so straight forward that it is probably easier to download a complete fresh spreadsheet from the database and create a new pivot table.
However if you have saved to your hard disk and you open back up and add some new rows you need to redefine the range. To do that, after you have added the rows, click anywhere in the pivot table say K4, then click the Options tab and in the ribbon that appears you will see near the middle, a button labelled “change data source” which you click. The select a table or range dialog box will re-open and you then simply need to reselect from G1 to the new end row of column I to get your updated table.
73 jim g0cqk