Selective columns

I have a workbook with 100 columns, names and numbers. Problem is, only so many are active at any one time. Is there a way that only the active columns are shown or failing that, are moved to the left to form an active block?

I understand I could "hide" the unused colums but that is a long and laborious task unless somebody knows a quicker way to do it.

Much obliged.

Thanks for your quick response.

I've got to be honest here and say that your instructions are way beyond my ability, I wouldn't know where to start.

The workbook is for a golf society. The columns have the name of the player in the first box, then a list of numbers (1-9) below. The template workbook shows the list of 100 names but no numbers/result are inputed.

Once I know who is competing in an event, I want to only show those players taking part.

I've downloaded the workbook so you can see for yourself what I'm talking about.

Sorry to be such a pain, like I said I could individually hide the columns I don't want but that would be a very laborious and time consuming way to get the outcome I want.

Just to add the sheet I'm trying to sort is the "Master".

I've thought of maybe a simpler way of handling this. If I add a row to the columns and then put a "1" in all of them (meaning in attendance) then before each event changed the "1" to "0" for those not taking part, is there a way to get the workbook to automatically show only those columns with a "1"?

If not, no worries, I can probably select the columns with "0" and hide them with one click.

Marcol,

Sorry, probably my fault, I have a notifer to alert me to new posts which didn't arrive until after I'd posted so didn't spot your new post. Apologies.

Again, the attachment is far beyond my abilities, I wouldn't know where to start.

I've added the extra row and can individually select the columns to hide. It would be great if there was a way to do this automatically but I'm just learning really.

Again much obliged and sorry again.

Wow, thanks for taking the interest.

Golf days are about three times a month.

It's a golf workbook that works out points scored, handicapping, etc. All things golf really.

The results page is exactly that, just puts the players in the order they finish on points.

The master is where you enter details of the course (par, stroke index, SSS) the scores from the players scorecards, plus their handicap. Players claim a score, this allows us to check that they are right. The page will identify scores under par, scores on par 3 holes and scores on stroke index 1,2 & 3, the hardest holes on the course.

The css works out handicap adjustments, whilst the cards page is just the sort of storeroom.

Some parts of it are quite clever (if I do say so myself) but others are very basic due to my limited knowledge.

The whole point was to try and develop a template that could be used on every occasion that ignored/hid players not in attendance.

Thanks for your interest but unless you understand the game of golf it probably wont make any sense.

Yes, every time we play it's a different course, the details are entered on the master page Columns A & B.

Would it help if I posted an attachment with a pretend competition showing half a dozen players? At least that would show you what happens and what I'm after.

I too am busy though today, off to work in 30 minutes, I'm very grateful for your help, I am learning, it's just a slow process.

Any more information would help greatly, especially if it shows the typical end result required.

There you go.

It's for a course in St Neots.

6 players.

(Obviously I'd hide the non-playing members, which is what this is all about)

Enjoy your weekend!

No worries, I'm pretty dumb, I guess most on here would know exactly what you meant.

Quite looking forward to seeing what Marcol can do with my little project.

What is the sheet "Attendance" for in your original workbook?

Could it be used to store the players' names, handicaps, etc?

Originally Posted by Marcol

What is the sheet "Attendance" for in your original workbook?

Could it be used to store the players' names, handicaps, etc?

I needed a way to get the results page to show the players who play at the top of the list so tried using an attendance page, just putting a "1" in the cell by each name used moved them to the top when adding to the formula in the Vlookup on the results page. I've sinced move the figures into a box on the "master" page (Row 28).

I have all the handicaps in a different workbook and add them at the start of each event. Do you think the two things should be put together?

Yes, better we can get everything in one book if possible.

I see that St. Neotts' has 3 tee positions White/Yellow/Red, the Ladies using the Red.
As the pars and stroke indices are different I take it that should be taken into consideration where ladies are concerned, assuming you have mixed games, or indeed even when it is a ladies or gents game?

The calcs in your book are quite difficult to follow but I think I'm getting there.

No doubt I'll have a few more questions later.

Okay got to go for a while, the 19th hole beacons... ...

Look in tomorrow.

Just thought of something to add before I retire for the night.

The CSS page will work out how many players played by how many handicaps are entered (It doesn't count catagory 4 players). It can't have handicaps added for all possible players as it will consider that all players had played and that would completely skew-whiff the calculations.

Morning Marcol,

Can you hang fire before you do anything else, I've come up with something to solve my original problem, just making the amendments now. I'll download what I've done as soon as I've finished so you can take a look. Any improvements you can see would be greatly appreciated.

Handicappers get strokes. An 18 handicapper would get one extra shot on each hole, a 36 handicapper would get 2. If a player has a handicap of 10 he would receive an extra shot on holes with stroke indexs 1 thru to 10. If a player had a handicap of 24 he would receive 2 shots on holes 1 - 6 and just 1 shot on holes 7 - 18.

Therefore a handicapper playing off scratch wouldn't get a shot on any hole, not that we've ever had a player playing off scratch. If the formula is suggesting a player off scrtach is recieving a shot on a hole then the formula is wrong.

The three figures are constants for all courses.

I have almost completed my changes which will allow a simple "sort" to show only those players participating. If you could allow me a few minutes to finish I'll post the new version. Much obliged.

Okay, that's a much more serious situation than that which the formula suggests, in short I think your tables will prove somewhat inaccurate.

Hang in there #25 posts without any tangible result must be pretty depressing, but better to get the ground rules established now rather than try to fix them later.

Hi Marcol,

Sorry it has taken me so long, a very time consuming piece of work.

I have attached a new template which makes showing only those playing easy with a quick sort.

As for what you posted, I'm not sure what has gone wrong then as it has always worked just fine. I wonder whether I've changed something vital when I've made these recent changes?

Anyway, see what you think.

Ps: I hope you're enjoying this, I'd hate to think you weren't.

With your Sheet "Cards" try this in D2

Please Login or Register  to view this content.

Drag/Fill Down to Row 20 (Hole 18)

This will return the following
A 36 and above handicapper would get 2 extra shots on each hole.
A 24 to 35 handicapper would receive 2 shots on holes 1 - 6 and just 1 shot on holes 7 - 18.
An 18 to 23 handicapper would get one extra shot on each hole
A 10 to 17 handicapper would receive an extra shot on holes with stroke indices 1 through to 10.
A nine and under handicapper receives no extra shots.

Is that now correct?

Hey! I wouldn't be here if I wasn't enjoying it, and it's to early for the pub! ...

No, handicappers aren't groups, they're individuals. It works like this.

A "O" handicapper recieves no shots.
A "1" handicapper receives 1 extra shot on stroke index 1
A "2" handicapper receives an extra shot on stoke index 1 & 2
A "3" handicapper recieves extra shots on stoke index 1, 2 & 3
and so on up until
A "19" handicapper who receives 1 shot on all 18 holes but 2 shots on stoke index 1 and so on.

The maximum official handicap for a man is 28, so 1 shot on every hole plus 2 shots on holes 1 through to 10.
The maximum official handicap for a woman is 36 so up to 2 shots on every hole.
Our workings out for easy use treat everybody as a woman so if they show a raise in the handicap over 28 we just ignore it.

Hope that all makes sense?

Originally Posted by Marcol

Okay, that's a much more serious situation than that which the formula suggests, in short I think your tables will prove somewhat inaccurate.

Can you just verify which table you believe would be inaccurate? I'm fairly sure it's not the points total as we use that to verify what score people have claimed so if there was a problem we'd have identified it pretty early on.

Are we talking about the CSS page and handicap adjustments?

The problem will arise in sheet "Cards" , it's probably "accurate" most of the time, but there can be conflicts.

I might have misread your first description of the handicap system, I think I see how it works now.
Hold in there, were getting there!

We certainly are, the original problem has now been sorted.

I'm now looking at way for the CSS to ignore those players not taking part.

As for the conflict, I'm not sure where it would be, it's never shown it's ugly head on match days as yet.

The handicap system is quite difficult to understand at first then easy when you know what you're doing. We still have expereinced golfers playing who can't get their head round it and don't know how to score their cards.

Have a quick look at this workbook to confirm extra shots for each handicap.

Change the Handicap in cell T3 to check the results.

Cheers
Alistair

Yep, that's it, spot on.

Good!
Your scores might be a bit tighter from now on, if you use that formula, I think you've had a few "gimmes" over the years!!! ...

Don't change all of your tables in sheet "Cards", all that can be done with one table, making maintenance much easier.

Is the formula in X4 and where it is supposed to go?

Can this be applied to the latest workbook I sent you named "Marcol"?

John

Let's take this step at a time!

I see that you have transposed the table in sheet "master", that's a good thing, you can now use filters to achieve many more things.
This is the basis of the solution I am working on!

Forget having the "cards" on separate sheets this can easily be done on one sheet.

Sit back with a glass of "Old Tom Morris" and enjoy ...
see this link
http://www.whisky-online.com/tom-mor...miniature.html

Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)

Okay but I'm actually on the pear cider.

I'm looking forward to seeing what you come up with, the idea that I will only require one card fills me with excitement and dread in equal measure.

I'll listen out for your next post.

If it's okay I'll send you exactly what I have now. I think it best I not touch it again until you're finished.

Perhaps a little water to aid the digestion, but equal measures, are somewhat excess I would suggest.

Could you check this out and update update Columns A:C as required

A/. Lady/Gent could be used for specifically defining the handicaps if ever required in the future.

B/. For filtering purposes

C/. Names will be easier if they are unique i.e. "Pearce. S" could be either "Pearce. Steve" or "Pearce. Stewart" this is not easy to lookup and usually leads to error. So use uniquely recognisable names.


What columns should the handicaps "Playing", "Correct", "Exact" be taken from to fill in sheet "Master", if any?

This sheet will control dynamically the final workbook, everything will update automatically if you add/subtract to/from or amend this sheet.

Done!

The reason there are some players who I didn't make space for was because they haven't played with us for 3 years. I still though keep their handicaps just in case.

Okay, the playing handicap is the adjusted handicap (Column H)

The exact handicap is Column E.

Now I've added a column (F) for the official playing handicap. This is the same figure as in Columm E but rounded either up or down, you only need the EXACT handicap for handicapping purposes.

Column F should have the same formula as Column E but I don't know how to do that, it keeps changing the source cells.

Okay, that's fine by me.
The reason for asking the status of players was to reduce the number of names that appear in the drop-downs I intend using througout the workbook, lapsed players could take up a large proportion of these lists, and might better be identifiable to avoid clutter.

Leave it with me, I'm building up a few more questions on what to me seems redundant duplicated calculations.

Might be tomorrow before I get back to you, I've just had a vision ... a glass of cold foaming ale with the condensation slowly running down the sides .. I often get visions on a Sunday, but never go to the pub before the kirk comes out, can't have the town dignataries talking about me, that would never do! ...

You can ignore the players who do not appear on the master I sent. I can keep them seperate and should they ever return I can soon add them. No problems.

You have a great night tonight, you've earnt it.

I'm about in the morning, at work in the afternoon. Enjoy and many thanks.

Quick one.

What's the difference between "pts calc" and "points" in sheet "Cards", they look the same to me.

Okay, when we first started this workbook many years ago we encountered a problem. The Points Cal column used to be the points column however we found in certain circumstances a "0" in the strokes column actually gave the player points, if you understand, a player gets 1 point more for every shot less he takes so a zero.......

Obviously this wasn't what we wanted so we put in another column which looked to see if the strokes column was "zero". If it was, then that is what was recorded, if it wasn't it took the points from the points calculated column.
To be honest with so many changes and improvements (believe it or not) over the years I don't even know if it's still needed.

Hope that is not too confusing?

I thought you were going out?

You are correct, if a 36 handicapper was to score a hole in one then they would indeed get 8 points. They'd be more chance of East Fife winning the Scottish Premiership!

The Par 3 calculation just counts up the score for par 3 holes, we have a cup for the best player of par three holes over a season. (We also sometimes have a par 3 prize when we have competitions over 2, 3 or 4 days.) The Par 3 total adds up on the results page.

This is a link to our website if ever you have trouble sleeping.

http://midshires.webeden.co.uk/

As we have now moved on from the original poser, I thought I'd jut better clarify a couple of things that I'm sure you've already sussed.

1) If a player gets the correct score for the hole according to his handicap, he receives two points. Then 1 more for every shot less, 1 less for one shot more (no score over that). This is called the Stableford scoring method.

2) When scores are tied the winner is decided by their points score on stroke index 1, then 2 and so on if necessary.

Number 2 is our own personal rule. In golf clubs they use whoever had the best last 9 holes, then 6, then 3. We could never see why somebody should win a competition just because their last 9 holes where better than their first nine. Much better (in our opinion) to decide it on who played the hardest hole the best.

Check this out to see if the scoring system is correct.

It seems to differ considerably from the original results, but does conform with what has been agreed so far.
Don't worry about missing data, that should fall in place once the scoring system is established.

Use the Drop-down in Sheet "Cards" A1 to change the cards for the sample players.

Why is the points total different to the stableford total?

From what I can see the points total is correct and the points ARE stableford points so you probably don't need the Stableford total (which is incorrect anyway).

It looks good!

Ah, I see now.

What you have down in the Stableford row would be the points total if a scratch player played. I don't know why you would need that but perhaps you have a reason, something to do with the working out?

PS: I leave for work at 2:15pm so if you have any other questions today you need to get them in before then. Wont be online again until tomorrow. (Bloody work gets in the way of everything)

Hmm..?

I thought that the Stableford rule only counted shots for adjusted par and under, does it count one over as well?

I might have misread this

Then 1 more for every shot less, 1 less for one shot more (no score over that.

I didn't expect both rules to return the same result, if it consistantly does, why do we need both?
What's the deciding benefit of one over the other?

Is this correct now?

That's correct now.

There aren't two rules, only one, stableford. The points total IS the stableford total. From what I can see you don't need the Stableford row.

As an easy example, you have a handicapper of 18. He gets an extra shot on all 18 holes. If the par on every hole is 4, then he should get a 5. That would give him 2 points. If he scored a 6 he would get 1 point and a 7 nothing.

7 = 0, 6 = 1, 5 = 2, 4 = 3, 3 = 4, 2 = 5, 1 = 6.

A scratch or zero handicapper:

6 = 0, 5 = 1, 4 =2, 3=3, 2 = 4, 1 = 5

A 36 handicapper

8 = 0, 7 = 1, 6 = 2, 5 = 3, 4 = 4, 3 = 5, 2 = 6, 1 = 7.

Sorry if I didn't explain myself better.

No problem, I think I see the difference.

In the more usual way of determining a golf score the aim is to get the minimum score e.g. -6 therefore someone scoring two over adj par at each hole would be +36, but stableford goes for a positive result where the winner has the most points, so the same score (+36) would be 0, maybe not quite so embarassing.

I thought the "points" total was the former, conventional method, and never gave it a thought as to why there was never a -ve # returned.

Just smile ... ... the reasoning keeps me happy ...

I''ll plod on and start building a prototype workbook based on what we have so far.

The better golfers, the ones you see on TV play "Medal" golf, so the lowest score wins. If we were to play medal (which we could) then it would be our total gross score less our handicap. This is fine if all golfers competing are of a good standard however the normal crowd you get have players that might take a dozen or more just to complete a single hole. They would obviously have no chance of ever winning.

Stableford means you play each hole seperately so to speak, so if you have a really bad hole, you shrug your shoulders and move on to the next, the day hasn't been lost. A high handicapper can score a lot of points even if he was to say miss out of half a dozen holes, that's what makes it such a great game. Under stableford I could play Tiger Woods. I'd almost certainly lose but it wouldn't be a walkover.

I'm off to work, you have a good afternoon.

Morning Marcol,

Thought I'd give you a bit more information regarding the "Adjusted" handicap, though it is quite complex so it may not be possible to include it within the workings.

Anyway here goes, this is something we came up with ouselves in order to "share" the trophies out a bit more, in the normal handicap system if a player is on form they can wipe the board for several competitions.

It's in three parts.

1) The winner is cut 10% of the handicap he played at. Quite simple.

2) Players are also cut 1 shot for every point over 36, less 1 shot for every other player over 36.

Example: Player wins with 40 points which would mean a 4 shot cut, however the runner-up has 39 points, third place 36 points, so the actual cut is 3 shots (4-1).
Just to make it a little more complicated the runner-up (because he has scored over 36 points) would also be cut 2 shots (3-1). Just to clarify if the winner scored 40 points and the runner-up scored 39 BUT there were three other playerrs on 37 then nobody would get cut.

3) The bottom third of the playing field get their adjusted handicap increased by the amount that has been cut under this system. (To keep balance)

Example: (22 players)
Winner (handicap 17) has 39 points, with runner up 38, third 35.

Winner gets cut 1.7 (10%), plus 2 shots (3-1). Runner-up gets cut 1 shot (2-1).
Total cut is 4.7.
The bottom third (7 players) have their adjusted handicap increased by 0.7.

That's it. Like I said, a bit confusing and random. It works but it may be something we have to do on paper rather than through the workbook. No worries.

Marvo

Looks fantastic!

The CSS is really complicated, to be honest I don't understand it, it was put together several years ago now based on the CONGU method of handicapping.
At the end of each compettition you usually find most players go up 0.1, That is the maximum.
If you play within the "Buffer zone" then your handicap remains the same.
If you go higher then you get a cut but this differs dependant upon the handicap (or Category) of player. 1 shot ovcer the top for a 5 handicapper would produce a cut of 0.2, whereas for a 28 handicapper it would be 0.4.
Several things come into consideration, the SSS of the course, other players scores, it is very confusing but the figures on the CSS page in the hidden rows do give the correct result.

If you need more help regarding this page I can get the person who made it up in the first place to elaborate more? He took the figures from the CONGU rule book.

You're doing a great job, I must consider taking an excel course, I'd love to be able to do half of what you can. My only worry is if I'll be able to put things right if they go astray.

I know there can be a lot of laborious work involved, if you'd like me to do any just send me the sheet and I'll do it. Leave you to the important stuff.

I'm just adding another download from an event from earlier this season at Shendish Manor It may help you understand the official handicapping more.

With your design, when an event is over do you just save a copy for your records and then move back to the template?

This might take a while to understand, the last w/book will definitely help.

Think I'm beginning to see how it works, remember I know nothing about golf, so hang in there.

I've not considered the archiving as yet, first thought is to build a separate workbook with limited functionatity and then export each competion to it.

Let's get the prototype solved first, step at a time, don't worry about the layout and appearance, that's easy to tart up later. We could maybe even replicate your club score cards if you like! How about posting a sheet showing one? It might be something I could work with when I can't get my head round more serious matters.

P.S. The Forum Database is regularly crashing again, don't despair, It's always recovered in the past, sometimes it is over an hour before it settles down again!

I tried several times with my last post before it finally was accepted, same with this post ...

When it's like this it's safer to copy your post to notepad as a precaution, until you're sure it's been sent ...

Not to worry about the archiving, I can just copy the results sheet, that will do. In fact that will be a good way of doing it for looking back in future seasons.

I like the courses page, I can add dozens to that. Don't need the white/yardage, that's for the professionals.

I haven't got a club scorecard, we play different courses all the time, however if you want to see one (they're all pretty much the same) here is a link to the St Neots card:

http://www.stneotsgolfclub.co.uk/the-course/

Remember we don't require the whites, only yellow and red.

As for the CSS page, I'll get the chap who made it up to detail the workings and post them. As simply put as possible of course.

Off to the airport in an hour to pick up some friends from Australia. I'm about though later and for the next two days so any questions just fire away.

Just to add, we regularly get new players (just had one for our next event a week on Friday), I guess your system will just require me to add them to the Handicap page?

If adding is going to be a problem, we can perhaps have "Guests" and just change the name?

Anyway I'll use the older system for that game, then send you the workbook.

The new Guy is Phil Beeston, 28 handicapper and obviously no adjustments.

The SSS is a way of judging how hard a course is. For example the par may be 72 but on a really easy course the SSS could well be 70. The CSS page uses the 70, not the 72 when working out handicaps alterations. They basically expect you to do better than you would expect to.

At St Neots the course is a par 72 and off white tees the SS is also 72. However off the yellows, whilst the par stays the same the SSS is only 70. For the ladies the par is 73, so is the SSS. What they are suggesting is that for men playing off the yellow Tees, the course is slightly easier.

Thinking of a scratch golfer (a zero handicap), a bogey is one over par, a double bogey two over par. That would also be their net score as there is no handicap toi take into consideration. Therefore dependant on your handicap a net double bogey would be 2 shots more than you are supposed to take. I hope that's not too confusing.

Example:

A scratch golfer playing on a par 4 stoke index 1 would have a double bogey if he took 6.

A player with a 1 handicap playing the same hole would have a net double bogey of 7.

With such low handicaps, neither players would score a point.

Hope that helps.

Hope that helps.

So the SSS would be best stored in the course data sheet?

Sorry Marcol, yes it would.

Here are the mathmatics of the CSS calculation:

Establish the composition of the 'field' as a percentage of each handicap category excluding category 4
Eg 10% cat1. 50% cat2. 40% cat3. (gives the the figures I J K on the CSS page)
Establish the percentage of the 'field' (cat1 + cat2 + cat3) with a score of SSS +2 or better (nett double bogey, which is derived by par - SSS + 36 - 2)
Eg 20% (gives the figure L on the CSS page
Then refer to table in appendix B. (the table at the top of the CSS page)
This example would give the CSS as the SSS +1

The cat1 cat2 & cat3 percentages are rounded to nearest 10 but must add up to 100%.

I don't know how clear that is to you. Hope it helps.

Taking the questions back to front, the RO can be removed from the cards page but MUST remain on the CSS page. Part of what that workings would mean that on occasions when everybody in the field played poorly, it could say that nobody should receive +0-1. We've chosen to ignore that advice should it ever be given as we wnat to reward people just for turning up so if everybody plays poorly, everybody gets +0-1 back. Makes no difference to those that played in reference to each other but does give them a slight advanatge over players that haven't attended. However like I said, this is just one part of a bigger formula so the RO must remain on the CSS page.

Now on to the AD2. There is a mistake somewhere in the formula in AE2 because if you take the Martyn Denton card as an example, we are asking it to multiply AC2 (-17) by AF2 (-0.3). 17 x -0.3 should be -5.1 however for some reason it is returning a plus figure!
On the made-up card for M. Denton he scores 53 points which is a massive total, almost unheard of, so he would definately receive a substantial cut to his handicap. Even so, if he had played badly the MAXIMUM an official handicap can be increased is +0.1.

I don't know if when this is put right it will answer your origianl question but if it doesn't, please ask it again and I'll take another look.

John

I've tried changing and adding new players which works out fine. How do I add a new course?

1, Entered the formula given, it returned TRUE.

2, Regardless.

3, RO stands for Reduction Only. This can give figures of 1, 2 3 or 3 RO, if it returned 3ro you shouldn't give any of the players 0.1 back BUT WE DO for reasons I mentioned in a previous post. However the chap who devised that page wants to retain the 3 RO in case we ever decide to reintroduce it.

Okay, this is difficult and deliberately over-complicated by CONGU. You cannot get the table refered to on the internet, only by buying the CONGU book. This table is the one hidden on the CSS page.

How is the Competition Scratch Score calculated?

This is not a straightforward calculation. Broadly speaking, the calculations follow

For Men; Discount all scores returned by category 4 players (handicaps 21 to 28), for Ladies; Discount all scores returned by category 5 players (handicaps 29 to 36)
Calculate the percentage of players who competed in the competition in each of categories 1, 2 and 3 [and 4 for Ladies],
Calculate the percentage of scores returned by the category 1, 2 and 3 [and 4] players which were two over the Standard Scratch Score of the course, or better.
Use the percentages from (1), (2) and (3) to determine the Competition Scratch Score by referring to a table supplied by CONGU for the purpose.

I don't know how to explain this any better, writing it down is very difficult.

If we can't work this out, the CCS page on the old book does work so maybe we should just use the results from that?



John

Just wanted to ask, what do you mean by adj par?

Adjusted par is used to help produce a stableford points total only, that total is then used for handicap adjustment. Adjusted par is not used in working out handicap adjustments.

This maybe where a confusion is arising. Handicap adjustments are only done on a complete round and not individual holes or part thereof.

Okay.

I've successfuly added a course (yippee!).

You must have meant

Change the course name, in say C25, and in the next free row in Column AA put e.g.

Column A.

Obviously one too many beers is equal to one too many A's.

I am now going to transfer the scores from our recent competition at Shendish Manor to see if they tally, easiest way of checking the workbook out.

Didn't get very far. I've added a course and a new player (all okay).

If you look at B.Adams it shows a result of 3 in Master box Z6. That should be a 7.
On the Cards Page, player B. Adams, box V13 the answer (7) is correct so somewhere the figure is changing. I don't understand the formula (as yet) to change it for you.

I also can't do anymore because for some reason the handicap for Baxter. W isn't transfering over, this then means the CSS page wont work which affects every player.

I've attached the workbook with the few changes I've made for you to put right.

Questions:
1) Can I just delete any rows on the courses page I don't require?
2) On the handicaps page I've coloured the name boxes, green = member, yellow = Active guest (played this season), red = inactive guest. Is it possible to do this automatically and then can the table be sorted alphabetically? (I tried but it says something about same cell size)

3) When all this is finished, will I be able to add any further pages without messing up what I already have or would I need to come back to you?

Note: As an example we have an "Order of Merit" which defines the player of the season. It's for members only and the points on based on a grand prix system, 15 points for the highest placed member, 12 points for 2nd, then 10 down to zero from 3rd to 12th.

The only reason I ask is that we had two workbooks, one the one we are dealing with and another for everything else, you know, accounts, members addresses, fixtures, etc. We/you have now moved the handicaps from one book to another (no point in having duplicates) so I think in future I'll move over everything that is "playing" based. Probably mean about two, possibly 3 pages.

John

That's what I did regarding Adams. B problem to solve it. Great minds and all that.

Don't know where I got the Baxter. W bit from. Anyway, another problem solved.

Started entering the Shendish Manor scorers, everything looking good. (Apart from the handicap adjustments which obviously we haven't sorted yet)

I don't even know what a VBa is but I'll go with whatever you think best.

Are you getting fed up yet or are you like me, look on everything as a challenge to be solved?

Okay Let's sort the handicap issues first.

I'm adding named ranges to the sheet "Handicaps" and refering to them in the formula.
This should make things easier.

Don't do anymore until I post the amended workbook.

Please don't reply to this post, wait until my next post, the board is getting very difficult to follow.

VBA involves macros, some people don't allow them.

You have duplicate entries for "Bryanton. Darren" & "Chudasama. Hamon" in the "Handicaps" sheet.

They have different values for their handicaps in both cases, as they are both "Guests - Non-Active" they can probably be deleted.

If however you want to keep a record of them, would I be correct in assuming it is the record with the lowest handicap that should be retained?

This sheet should not have any duplicate entries for members or guests.

Hi Marcol

just spotted a slight anomaly with the handicapping on the cards page. On the original I had the formula in AE2 and AF2 as hidden as they were for calculation purposes only and the cell AD2 had the actual adjustment in it. Problem now is as the formula in AE2 can't show the +0.1 so it gives an unchanged result when it should be +0.1. Think this may have crept in from Marvo Changing the original.

Andy

Responding to your post:

1) Yes, they refer to the correct ranges.

2) Regarding sort, if you use that button the names move but the other columns stay as they are so you end up with wrong adjacent information. I also notice that they no longer work, for example if you alter the figure in K3 it doesn't alter E3?
If you do the longer version of sort then it also sorts your title row which ends up halfway down.
To be honest I think I'd rather have all the names listed alphabetically, easier to find in the drop down list. Better still would be all active players alphabetically and then the non active players. I can probably do this myself when the workbook is complete.

3) The Bryanton. Darren is 28, the Hamon Chudasama is 20.7. The other rows should be Brett. Steve 19.8 and Brooker. Gordon 13.5.

The problem with deleting non-active players is that they can easily become active again. Funnily enough Bryanton. Darren was supposed to play in our last event but injured himself so had to withdraw, whilst Chudasama. Hamon is scheduled to play in our next event so will become active again.

Finally, I've got rid of the merged cells that you mention (no problem), feel free to get rid of any you find.

I've a full day at work tomorrow so wont be available to answer any questions until the evening.

Many thanks

John

Solved the problem of the handicap increase of 0.1

changed the first result in the formula of AD2 from the text string "+0.1" to the value 0.1 then change the cell reference in Y4 to AD2 instead of AE2. sorted! works great with all the changes you've made.

The 3 RO or reductions only is just there as a visual indication for me so I could see that when the scoring was exceptionally low (the percentage returned in F70 on CSS page was in the range of column F of the hidden data table) the +0.1s would be ignored. For the purpose of getting the +3 value in the adjustment to the SSS I put both of the +3 together so the IF statement worked. The "RO" was added on separately just for information, but its no longer used by the society.

Okay that simplifies somethings and complicates others, I've been working on the official version I found, not to worry it's not a problem to change.

I have the bones of an archiving sheet prepared.
What old records do you have? It might help with testing, and working out your annual compititions, etc.

I think I would scrap all the data on the sheet "Handicaps" from column K onwards, and replace this with the new archive sheet, it would then keep a record of handicap changes and update as the competition/matches progress,

Some stuff, already done, is beginning to look a bit redundant now, but I'll leave it as it stands, it might help later when trying to solve similar problems in Excel.
At least it will show you different ways to arrive at the same answer.

I have stacks of old records, what do you want?


If I scrap all the detail on the handicap page I'll have no record to show to somebody how they have reached the handicap they have!

I was actually thinking that if each name had two rows associated, one with official adjustments and one with unofficial adjustments it could be set up to add the entire row. I could also reverse it so that each new event was in the first column (added with each event). Only problem is I remember you saying that each name can only have one row, no spaces.

Ideas?

So sorry you have done unnecessary work and I apologise for the time lost. I know how you feel, I made up 100 reversed scorecards (Didn't know then about the transpose button) only for you to tell me I only needed one.

Living and learning all the while.

John

If I scrap all the detail on the handicap page I'll have no record to show to somebody how they have reached the handicap they have!

Au contraire, mon ami!

The new archiving system I propose will be much better, well I would say that wouldn't I?
It will be in the same workbook and fully audited and filterable so you can show anybody what they have done for as long as you have records.

Just a few samples should be enough, I need to see the layout and data types they contain.
If they are in one file say the last 6 months?

Hey, no need to be sorry, I've undone and re-worked what I managed today and I'm back on track!

Sorry Alistair, what is it you actually want?

Workbooks from previous events?

The handicap adjustments shown on the handicap pages are from events this season.

This society was formed as new at the start of this season, a breakaway from the society I formally ran. The handicaps in column I are the starting handicaps for the Midshires society.

I do have workbooks from previous seasons (Crown & Cushion GS) but this is the first season we have handicapped in this manner regarding UNOFFICIAL handicaps. It's been a work in progress for several years.

I tried to post the workbook from our latest event, the three dayer at Wychwood Park but the file size was too big for this forum.

Anyway tell me exactly what you want and I'll do my best to provide it.

John

Okay have a look at this it's still in progress, but it might help you see where I'm coming from.

Try zipping the file, then post it.

Sorry got to rush, my supper's going flat ....

Hi Alistair

Had a look at the interpretation of handicap, unfortunately the ladies part was discontinued i that format in 2004. They scrapped category 6 and made the maximum for category 5 36.

strictly speaking the ladies should not be included in the CSS page as they effectively play a different course to the men, and it usually has a different par, SSS and stroke index. The answer would be to have a separate CSS page for them, but as the only two active members are both category 5 players they wouldn't count so theirs will always be the SSS for the red tees

No sorry, still don't understand where you're coming from.

Some things aren't important to us, no necessity. For example white tees, yardages for white and red, records of scores on stroke indexs, (they're just tie breakers),
Names of holes, addresses for courses, etc, etc.

There's a lot of input work there which has no usage.

What the workbook was originaly designed for was to check claimed scores. Doing it manually was a very long winded process and open to human error. A player comes in, claims he got 33 points. We input his scores and if we agree with the score of 33, job done.

Things were added to, handicapping being the main extra but also the automatic results page.

For records, all I require is the result sheet (Master A1:D23) and handicap changes. The result sheet provides me with the Par 3 total and finishing positions for the award of Order of Merit points and UNOFFICIAL handicap adjustments. The master sheet will give me the OFFICIAL handicap adjustments. That's it.

I have downloaded some of the workbooks for this season, plus part of our general workbook for you to get some idea of what and where we are at.

Yours, John

1/. Regards the "Courses" sheet
This is pretty well the information as made available by all clubs, with the possible exception of hole names.
If you enter the data as it is required, it is there for ever as a database.
Things like addresses, course type .. etc are just examples of notes you may or may not want to add.
Just keep the format of the tables the same for all courses and add the course name to the list, the formulae in the workbook take care of the rest.
Changes made to data here will be reflected throughout the workbook.

2/. To check a scorecard
With Sheet "Master"
Select the course and tee
Select the players name from the drop-down and enter the returned strokes
Then with sheet "Cards" select the players name from the drop-down in A1.
All the information you need, and more, is there.

This will not automatically be archived, the finished workbook will ask you to confirm if you want to save the info so you can check any card at any time then keep/save/delete as required..

3/. The idea with archiving is that once the desired results are saved, the sheet will update the handicap sheet with all the new handicaps, official and non-official.
This will then be called for any subsequent rounds entered.
The archive can then be filtered for player course etc and you can see his/her progress.
Order of merit, par3 competitions etc will all be extractable from this data.

At a quick glance I don't see any dates for the rounds played. Dates are very useful when filtering and calculating further information.

Presentation of cards, results, etc can all be displayed in the format you would prefer once we get the database established.

I'll work with the sample sheets you have posted, but I have a few other things to do this weekend, so I might be a little slow with further posts.

Cheers
Alistair

Dates.

Bedford 11/3/11
Le Touquet 13-15/3/11 (3 rounds)
Pavenham 4/4/11
Abbey Hill 22/4/11
Turkey 6-13/5/11 (4 rounds)
Overstone 16/5/11
Priors Hall 3/6/11
Shendish Manor 24/6/11
Wyboston Lakes 27/6/11
Wychwood Park 15-17/7/11 (3 rounds)

John

Something for you to ponder at your leisure.

It is my feeble attempt to produce a table to work out the UNOFFICIAL adjustments.

Much to my surprise, it actually works except column L where I'm trying to get the result that if ever the handicap adjustment for the winner (only) is less than the plus adjustment they have prior to the event then it returns a zero.

Example: Player Adams has a plus adjustment of 4 shots. He wins an event and gets cut 3 shots, which would still leave him with a plus adjustment (+1). I need that to be zero.

My table is very basic, I'm sure you can tidy it up a bit. This then could be encorporated into the main workbook.

You have a great weekend.

John

Thanks for the dates, we'll do the handicap adjustments shortly.

Forgot to ask, could you amend Handicap Interpretation.xlsx (Post #87) to suit your needs, both ladies and gents

Done as per request!

Hi Marcol,

Unofficial handicap adjustment sorted !!!!!!

Check it out, just increase D2 to anything above 3.8.


I know you'll find a better way but can't tell you how chuffed I am.

John

Another download for you to consider, this time the handicap page.

It could be limitless.

Try out this workbook

I have adjusted the formulae to suit Copy of Handicap Interpretation (amended).xlsx

Just check the first 4 sheets, the rest are still in progress, in another file.

It would be handy if you could fill in the courses, preferable minimum would be the S.I. and par for the yellow and red tees + SSS, the formula defaults to standard par if this isn't entered.
Keep the format and layout as is for now, it won't be time wasted, and will save me a fair bit of time, when testing the archive system.

There you go, five more courses added.

There is an error on "Cards". When you click on any of the ladies the Tee still shows as YELLOW, so you get the wrong score.

Haven't got round to the ladies from red tees yet, still based on yellow, is all else okay?

The extra course data will be a big help, thanks. Probably tomorrrow before I post again.

Everything looks okay.

Just wondered where are the handicap changes going to be amassed, we originally had them showing on the master but now they only appear on individual cards?

Knowing if there has been a handicap alteration for individual players is something members are quite keen to hear on the day, they especially like to know how much a competition winner has been cut.

Perhaps talk again tomorrow.

John

That will all go into the archived results, hopefully I will have an example later today.

In the meantime this sheet might help you when checking, and later on, archiving your data.

1/. With, for example, Abbey Hill.xlsx Sheet "Master"
Copy the entire table, in this case Range A1:AK26
Then with Sheet "Transpose" select A1
Paste > Paste Special > Values Check the Transpose box. > Click Okay.

2/. In sheet "Expand Name" in column D there will be a list of the Short Names for Abbey Hill players and in column E the full names.

These columns are highlighted with conditional formatting to show potential errors
e.g.
"Huband. P" is either not in the list Short Name, which is an initialised form of Full Name (in turn a copy of the names in the sheet "Handicaps"), or as in this case it is a misspelling.
"Pearce. S" as we noticed before could be either "Pearce. Steve" or "Pearce. Stewart"
again in this case the result is correct but if "Pearce. S" is only once in a record, then it will only ever return "Pearce. Steve" you should check that it wasn't "Pearce. Stewart" that actually played.

3/. Copy the list "Result" and paste values to say Column H, make any changes that are required then copy that to sheet transpose, or copy directly if no changes are required.

Add any missing names to the members in "Handicaps", and also to Colum A of this workbook, (drag down the formula in Column B and sort Column A alphabetically and save)

Once you have transferred the required data to whereever you need, sheet "Transpose" can be cleared and used again.

That should save you a bit of typing.

Very clever. What's the simple way of clearing it?


Amendments made to handicap lists.

To clear the sheet "Transpose", click the top left corner of the worksheet (triangle icon at the top of the row numbers). Press delete!

Remember to also clear anything you have temporarily pasted to Column H in sheet "Expand Name"

This is only a tool to help convert old records.

The attached is an example that used the tool to get the basic data then columns were added/deleted to get a simulation of what the archived data mighy look like.

Try sorting and/or filtering to see what any players' record is for the data period covered or whatever you else might need to know.

The results are obviously limited in this example, but when the archive database is finally established there will be many more options available with no added work on your behalf.

What changes have you made in Copy of HandicapScoreCards-master2.xlsx?