Trying to get different "comments" to print on different sheets within same worksheet

[spelingchampeon]

Using MS Office Pro 2010 to design and maintain the excel file. Most of the members are using Open Office to view my weekly file. I'm not an excel person, I just like to tinker and learn what I can.

I run a family Nascar pool with 10 members. On the main page in column A is the where the event takes place (Daytona etc etc). Across the top in row 1 is each members name. For each race I put the total points awarded after each race, in each cell, and use a mouseover to see the "comment" which shows the driver they selected.

RACE		Ann	Mark
Daytona - Results		37	33
Phoenix - Results		28	21

<tbody>
</tbody>

If Ann looks at the main sheet 1 (Races), and was to put her mouse over the 37 for Daytona, her driver (Biffle) would come up in the comment. Because each member can only pick the same driver 3 times throughout the entire year, I'm trying to set my excel file up with a sheet for each member to glance at the driver totals. Ann's sheet would be sheet 3 (renamed Ann). Mark would be on sheet 4, etc. My intention is automatically have the mouseover comment for each week to show up on everyone's individual sheet, so they can then sort the column and see how many times each driver has been selected. Not everyone is computer savvy, so I'm trying to make this as easy as possible.

I have gotten to a point where I could manually enter each driver on sheet 2 (Driver), and then go to each sheet and for each score cell type: = and then go back to the driver sheet and hit ENTER to get the names over, but it really makes for a major workload each week. I did stumble upon a site that had me do this:


Function CopyComment(cel As Range) CopyComment = cel.Comment.text End Function If you want to copy the comment of the cell sheet1 A1 in to cell sheet2 B2, you can do it by using this new function.

The formula in sheet2 B2 will be:


=CopyComment(Sheet1!A1)

While this helped with putting the TEXT that I typed in, it does not put the comments in. I hope I explained this thoroughly, but if you have any questions, fire away.

Any help would be appreciated. Thanks!

 

[Zack Barresse]

Hi there,

I'd recommend a totally different structure and NOT to use comments to house data - especially data which must be validated. You could use a table to track this information, as well as a list of drivers, and a column for the max driver picks (assuming this would be per season).

I'd recommend a table setup with the following columns:

Race
Season
Person
Points
Counted*
Driver Picked
Unique*
Valid*

* column would be calculated.

Assuming you used a table and it was named "Data" and the column names were as I proposed, your calculated columns would become:

Counted: =IF([@Valid],[@Points],0)
Unique: =[@Season]&[@[Driver Picked]]
Valid: =COUNTIF(INDEX([Unique],1):[@Unique],[@Unique])<=MaxPicks

This also assumes you have a cell named "MaxPicks" with the number of maximum driver picks per season someone can choose.

I've done these things in the following file:
View attachment NASCAR Results.zip

Does this help?

 

[spelingchampeon]

Zack,

Thanks for the recommendations and file (I see the names Ann and Mark are quite popular ). Just to let you know, the people in my pool are not computer geeks. Simple works best for them, so the less they do each week, the happier they are. I just want everyone to make their picks and hope for the best.

If auto-copying the comments to text is not possible, I'll get used to supplying them with the driver totals through my own updating. I'm not against that at all (I've counted the totals out each week in previous years), it's all good in the long run.

One thing I've learned over the last week, trying to get answers is, Excel is one VERY misunderstood program. I had no clue to it's abilities, none whatsoever.

Thanks again!

 

[Zack Barresse]

I'm not sure I understand. The UDF you posted will put the comment text into a cell. Is that not what you're after then?