Solved Sorting my oldest to newest done automatically

April 22, 2013 at 11:53:14
Specs: Windows 8
I enter golf scores every week. I take the oldest date and replace it with the latest date then I sort it so that the following week the oldest date is again replaced with the current date. Below the date is the score so when I sort both rows (date and score) are sorted together. Is there a formula which once the sort is done, the next time I change the oldest date to current it will automatically sort without my having to resort each time. The end result is that the newest is always at the end.

See More: Sorting my oldest to newest done automatically

Report •


#1
April 22, 2013 at 13:21:21
✔ Best Answer
If I understand you correctly, you have something like this:

       A           B          C          D            E
1   5/2/2013   5/9/2013   5/16/2013   5/23/2013   5/30/2013
2      95         97        102          93         110


You then want to replace the 5/2/2013 with 6/6/2013 and end up with this:

       A           B          C           D           E
1   5/9/2013   5/16/2013   5/23/2013   5/30/2013   6/6/2013
2      97        102          93         110         98

If that is correct, there is no formula that will force an automatic sort. You would need a macro.

I don't want to spend time writing a macro if my initial assumption is incorrect, so please verify that I understand what you want to do and that a macro based solution is acceptable.

If there are any more details that you think I need to know, please provide them so that I can get as close to a working solution as possible. Please keep in mind that I can't see your spreadsheet from where I'm sitting, so the more details you provide, the better.

Note: If all you want to do is show a certain number of weeks, e.g. the last 5, then a formula based solution is possible as long as the last 5 weeks can be displayed somewhere other than where your existing data is now. It is the fact that you are overwriting existing data that makes an "automatic sort" require a macro. If you were willing to add the latest data to the end of the existing data, then formulas could be used to display the last X weeks in a different location, e.g. on another sheet. You could then use that sheet for whatever you doing with your updated data now.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 22, 2013 at 15:56:34
You have what I want to do correct. The handicaps are based on the last 10 scores that they have done. There may be a way that automatically drops the last score or counts back ten scores, but I haven't been able to figure how to do it. I would rather the macro, but I don't have enough expertise for that either. There are two of us who do this, both of us senior citizens so all of this is fairly new to us.

Report •

#3
April 22, 2013 at 19:12:54
If you are willing to start adding the last piece of data to the end of the row instead of the beginning, this formula will always sum the last 10 values in a row, in this case Row 2.

=SUM(OFFSET(A2,,COUNT(2:2)-10,,10))

Note, this assumes that there are no blank cells from A2 out to the end of your data.

Does that help?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 28, 2013 at 04:54:00
Unfortunately my spreadsheet has blanks because sometimes the golfer did not golf on all the dates. I do, however, have a followup question. If the number I want cannot exceed 20, how do I show that in a formula. i.e. you have a sum total/10-27, but if the final number exceeds 20, I just want 20 to show.

Report •

#5
April 28, 2013 at 04:56:53
I have done that but I need spaces. I have another question. I add the ten scores, divide by 10, subtract 27. If the final number is more than 20, I need it to be no more than 20. How do I do that?

Report •

#6
April 28, 2013 at 04:58:57
Since this is a separate question, it should be posted in it's own thread with a relavent subject line.

It looks like your original question is going to require a macro. I can't work on anything today, but I'll see if I can find the time early this week.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question