macro to match multiple cells against a database and select multiple cells to copy/paste

Hi,

I have an issue that for the life of me I can't solve. I might find it hard to explain but I'll my best.

I want to be able to create a macro which copies 2 cells (next to each other) and pastes those cells as values (in the same cells) over the formulas that created them as they are only needed once.

These cells however are related to a name, therefore as the cells are pasted over and the formulas are discarded, I need the macro to be able to match a cell against the name in the data base, select those cells and paste them.

Now it gets a little bit more complicated. There are 5 different cells which could have names that I want it to be able to match against.

Is it even possible to match and select more than 1 cell? I also need help with having 5 different cells to match from.

Any help would be greatly appreciated.

I suggest attaching a workbook with a mock up as suggested below.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Where do you enter in the information for the person's first round? And which are the 5 cells you wish to match?

I still don't understand how the first round data is collects. I put my name in the form that comes up when selecting the Add Scorecard Info. It added my name and then the code crashed.

This looks like it crates a scorecard for the week indicated. How do I know how many games any particular player has played?

Is it that I create a scorecard. The players play. You fill in the scores. Then the magic button is pressed and the program goes out to the player database and fills in the next available slot for that player until the first three slots are filled in. So it may be possible to have a game where a player has all three slots filled in because he's played 172 games. Anther player has two slots filled in and yet another has none filled in. Is that the concept that you want?

I'm going to disable the part of the code that's crashing. I can see that it is intended to do some sort of error checking. I'll just avoid making errors.

The way I am reading the requirement is that a golfer has no handicap until he/she has played three rounds?

This application should really be handled by a database rather than Excel. However, Excel can be programmed to act like a database, but it would take a bit of overhauling the organization of the worksheets. If we can get the data "normalized" then we can use pivot tables behind the scenes to do some of the heavy lifting.

I have some additional questions:

Do you want to keep a historical data base of games by hole or will the round summary be sufficient? It doesn't matter much.

On the course data sheet, Column B is the hole number and Column C appears to be Par. What does Column D represent? You are dealing with someone whose knowledge of golf is exceptionally limited.

I am looking at the scorecard sheet. I am confident I can reverse engineer the formulas, but I may have questions.

I think it's neat how you overlaid the input boxes on the Add Scorecard Info over the picture.

Finally, I make no promises. This will take some time to organize (or organise - at least spelling is the only concern. No regional settings to contend with) and I will work on it as I can, so it may be a while.

I think I can take a shot at this "from the ground up."

You'll have a form that will allow you to enter new players and this information will "feed" one table.

You will have the form you have now to set up a round giving the course name and selecting a variable number of players up to 5. this will generate a sheet (or maybe even another form) so you can enter the score by holes. You are correct, it is probably a good idea to have a separate sheet for each player. So after the data entry, the scorecards will get parceled out to the player's sheet and this temporary sheet will go away.

I will probably have another table that will get maintained automatically after the scorecard entry that will keep track of the golfer's summary information to know how many games were played and what the score so we can calculate handicaps

I could probably reverse engineer the formulas you use, but it would help a lot if you could explain them to me and what needs to be kept and what could get discarded after the calculations are made.

I will make several assumptions: each course has 18 holes and no player will play more than one round on a given day. If this isn't correct, let me know.

I have a very simple question that I hope has an equally simple answer: how do you calculate a handicap based on an average?

This is what I meant. it's actually all quite complicated.

I'll try to explain it as best and simply as I can.

For each hole only scores of par+2 can be counted so each one gets an adjusted score. This can be seen in the hidden columns between each players name on the scorecard. Once you have the total adjusted score you find out the adjusted gross score by taking this figure and deducting the SSS (standard scratch score - the standard score someone with a handicap of 0 or 'scratch' would play all 18 holes in deemed by the club, usually around one less than course par). it is then that when this is done 3 times, you add them together and divide by 3. if this is a number above 28.5 and you're male, your handicap would be 28.5 as that is the highest, females are 36 and it's also different for juniors but I'm unsure exactly about their highest handicaps. The playing handicap is just the rounded number but as 28.5 is the highest, that is rounded down where as all others are rounded normally.

The points are used for ongoing calculations with is probably more complicated where if you have certain criteria, you go up 0.1 (only amount by what handicap can increase) and can go down up to 0.5 based on other criteria. May need explaining later.

I believe I may have only included males in my formulas as was only originally intended for me and my friends rather than any new players I might play with.