Dynamically copy comments from one worksheet to a cell in a summary worksheet.

I'd like to post an example worksheet but trying to "anonomise" the sheet breaks the functionality to the point that is becomes very difficult to see what's i'm trying to achieve.

we have a complicated schedule that I'm trying to simply for the end user. I have created a summary sheet with two drop downs that power the formula below:

=IFERROR(INDEX(Rota!$14:$14,1,SMALL(IF(Rota!$C16:$C16)=$E$2,COLUMN(Rota!"$C216:$C16))),2)),"") - (this example returns a target cell of $G$63, within worksheet Rota)

So that in essence I can create a "diary" for each employee of their work duties from the "rota" what I'm trying to do is now determine if the target cell above has a comment and then copy that comment to the cell.

so in VBA terms I need to be able to replicate the formula above but make it draggable across several cells, and then copy the comment from the worksheet "Rota" cell (above) to each cell in the range.

I hope this makes sense?

The kutools excel add-in will allow you to create a worksheet or workbook listing all comments, the author of the comment, and the worksheet and cell references to locate the comments.

Thank you - I managed to solve this myself - was driving home and the answer hit me like a sledgehammer I split the formula to give me the destination cell in a help column and then passed this as a string in vba to find the target cell.