Best way to display text from another field in spreasheet as a comment on hover

[jancliff]

I'm not sure if I will explain this properly but I am not sure if this is the best way to do this. I have been asked to create a dashboard of a calendar for data relating to two different types by day.

Attached is a spreadsheet showing part of the problem. There is a spreadsheet called "Calendar Overview" that is being used as part of a dashboard to highlight the calendar items listed in worksheet "Admin".

In "Admin" the user selects a day and adds a data type from the drop down values of ;
Off work
Other
Performance Targetted Key Competition
Squad Training
Training period

And then adds a comment in the appropriate row in column E

I would love the user to be able to hover or click in the Dashboard spreadsheet " Calendar Overview" in a day and they see the corresponding comment.

For example if in the "Calendar Overview" spreadsheet I clicked on cell C4 - I would love the user to see the comment from the worksheet "Admin" E2.

I have searched to see if it is possible to allow the user to hover and found that I can add code to the worksheet activate call. But I am struggling with it as I am looking across at values and not down as many of the examples have shown.

Any help in the right direction would be greatly appreciated. I'm not even sure if there is a better way to do it as I have racked my brain and got a lot of mush.


Many thanks

[jancliff]

I found this piece of code which will look at a list of cells from row 1 to 10 in Sheet1 and take the corresponding text in Sheet2 . It works beautifully.

Please Login or Register  to view this content.

I'm struggling to adapt it as it needs to go across and not down if that makes sense. I was wondering if someone could advise if it would be possible.

Many thanks

[nigelog]

see attached

your references cells(i,1) are in the order of (Row,Column) just switch so column is changed not row

[nigelog]

Please Login or Register  to view this content.

Hi, This will start you in the right direction. You will have to combine with index match for the Overview Sheet to change comments for each month. I dont know also is it possible to display a comment without the red corner tag. Too many of them and the sheet looks cluttered

[jancliff]

Thank you so much I will look at it. Very grateful for all your help. Its a bit of a huge task Ive been asked to do and I think its beyond me but I like a challenge so will plug away....

[nigelog]

Ill have a look at this evening and see if I can find a solution that does not require a worksheet for each month

[nigelog]

Hi jancliff

All the rest of the Calendar overview worksheet is an index/match of the Calendar Admin 2019 worksheet so will automatically update. I added the comment add to a button as worksheet activate would slow down everything, probable best called on workbook open

See if the attachment gets you any further down the line.

I couldn't find a way to get rid of the message indicators but found ways to recolour them. What I was trying for was that the indicator colour matched the cell colour. I'll keep looking into that

If this is the way you want to go you might start a separate thread to see if the comments can be loaded from an array. This is a slow method of loading the comments

[nigelog]

Finally sorted a sub 3 second method of updating comments on calendar overview

Please Login or Register  to view this content.

[rorya]

You could add this function in a new module:

Please Login or Register  to view this content.

then in your calendar cells, call the function and pass the cell whose value you want, plus the cell whose comment you want. For example, in C4, you'd enter:

=getdata('Admin 2019 Calendar'!D2, 'Admin 2019 Calendar'!E2)

[nigelog]

Sounds interesting Rory - but this was an exercise I got stuck on. Think I'll leave well alone LOL

[jancliff]

Tank you both so much for all your help. I will look through and see if I can understand it fully. Very very Grateful....