Mirror a cell's entire content including hyperlink and comments

[dontfcuk]

Hi all,

I have cells in Sheet 1 which each have a different hyperlink and a different comment when you hover over the cells.

In Sheet 2, I have used an Index with multiple dropdown menus to selectively pull up data from Sheet 1.

The PROBLEM:
The hyperlinks and comments are not following over to Sheet 2 but I really need them to.

The Youtube link below is what i used to build my indexing system. I'm very far along using this system so it could be difficult to change the whole indexing system if that's what you're solution is, but i'm opened to suggestions.
I'm really just hoping for a modification to my current formula so that it may pick up the hyperlinks and comments from the cells that are being referenced in Sheet 1.

My current Index Formula:

Please Login or Register  to view this content.

Youtube link to my indexing system:
youtube .com/watch?v=jMFsA1dcXb8

Thanks

Edit: I'm not yet aware of any possible solution. However, if the only solution is to use VBA, then I may have posted in the wrong section, and apologize in advance to the moderators of this section of the forum.

[BatterBits]

you will probably find this helps - at least with the URLs

[Glenn Kennedy]

If you need further assistance...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[dontfcuk]

Hi Glenn,

Thank you for your response.
I have spent all morning desensitizing my spreadsheet and removing all items necessary which wouldn't alter the results. You'll notice that in my first Sheet named 'Criteria Tracker', I won't have been able to leave you merely 10-20 lines because doing so will have messed with my Index formula, so i left it all there.

"Approximate number of rows for the solution to work with", I would say 1,000 to 10,000 cells. Essentially, once the formula has been found/created, I'll be able to apply it to all cells necessary. This is a spreadsheet that gathers data over time so there's no limit to the amount of cells that will have need this formula.

I hope my spreadsheet is as clear as you expect it to be.

Thanks again for the help.

[Glenn Kennedy]

Oh right!! I'll give it my best shot... in the UK morning... as I'm away shortly.

[kvsrinivasamurthy]

In Q2 then copy down.

=HYPERLINK(IFERROR(INDEX('Criteria Tracker'!$A$4:$AS$2001,$M3,COLUMNS($N$3:BD3)),""))

[Glenn Kennedy]

KVS... Look at Q2 on statistics. he has that formula in place already.... That's not the question. But, to be honest, I don't know what the question is!!

I'm on a metered connection for about another week and will not be downloading a 1Mb "sample" file again!!

[kvsrinivasamurthy]

Dear Glenn Kennedy
In the formula of dontfcuk cell content appears as text even though hyperlink is there. Formula is changed so that cell content appears as hyperlink.
I feel dontfcuk wants comments are to be copied by formula. I don't think it is possible. It needs VBA coding

[Andy Pope]

It will require VBA to deal with comments.

You can use a UDF (user defined function) to return the address for the hyperlink function as well as doing some comment work.


Statistics!Q3: =IFERROR(HYPERLINK(COPY_COMMENT(INDEX('Criteria Tracker'!$A$4:$AS$2001,$M3,$Q$1))),"")

$Q$1 contains the column number to return. Currently you are calculating it on each row but the formula you use would suggest this is applicable to all rows.
Using a range outside of that which contains the udf prevents multiple calls and formatting the comment with the wrong texture.

here is the COPY_COMMENT function, which is in a standard code module.

Please Login or Register  to view this content.

In your example the comments do not have any text. The UDF does copy over the text but it does not format it.
Is your real workbook has formatted comments you will require extra code to mimic the format

[dontfcuk]

Dear Glenn Kennedy
In the formula of dontfcuk cell content appears as text even though hyperlink is there. Formula is changed so that cell content appears as hyperlink.
I feel dontfcuk wants comments are to be copied by formula. I don't think it is possible. It needs VBA coding

Not quite. If you copy a comment to a cell, it will stick to that cell even if the information in the cell changes.
I'm not sure what other word to use, but i'm really trying to "mirror" the cells my index is referencing. So if the cell has a comment and a hyperlink, it will bring it over. But the references need to be dynamic if you play with the filter's in B3 to B9.

Hey Everyone,
really appreciate the help. We're not there yet though. Andy Pope, I opened your spreadsheet and the first 20 results in Q3 to Q23 were blank with comments. No hyperlinked text in the cells. I double checked to make sure your UDF was there and it was. Not sure what happened there. I can post a screenshot. Again, all i did was download your file which has macros saved in it and then i opened it. The attached picture is what it gave.
Unless the formula isn't working properly, the other problem I noticed is that the comments that appear in column Q are static and permanent in those cells, if I change my filter criteria, they aren't updating by showing me the comments of the new cells being referenced.
To answer your question, no, there will never be text in the comments, so formatting isn't important. Only the picture needs to be brought over.

The whole statistics page has dynamic results depending on the filters you choose from B3 to B9 and G3 to G9. In the sample file, only 4 filters can be changed, I kept it that way as an example to show you how the statistics change, and how the results also change in O,P and Q. I would like the comments to update as well when the filters are changed. The comments must belong to the same cell as the hyperlinks from the 'Criteria Tracker' Sheet in column AQ.

Thanks again everyone for your help . It's all really appreciated. If you have any questions, please ask away. I know I have a hard time explaining myself so it might not be obvious to understand what i'm looking to achieve
Cheers!

[Andy Pope]

Try this revision.

You will need to add a helper column so the code knows where the comment should come from.
add code to Statistics worksheet

Please Login or Register  to view this content.

this sub to transfer comments

Please Login or Register  to view this content.

[dontfcuk]

Hey Andy,

my lack of VBA knowledge is preventing me form fully understanding how to implement the 2 codes you posted.
Do I simply need to copy-paste these 2 codes in 2 separate modules?
Once that's done, how do I tell the code which helper column to get the comments from (I'm also confused about how to even achieve this with regular excel functions.)

Would you be able to use the sample excel sheet i sent you to show me how it's done?
Thanks, i really appreciate it!

[Andy Pope]

Here is a working example.

As I said the Change event code goes in the sheets code module.
The COPY_COMMENT routine goes in a standard code module.

The event code will be run when the contents of B3:B9 changes.

[dontfcuk]

I apologize for my radio silence. I haven't been near my computer and probably won't be until tomorrow or wednesday. I'll have to get back to you.
Thanks so much for the help!

[dontfcuk]

Hey Andy,

Thanks again for your help and I apologize again for not getting back to you sooner.
So, I took a look at the document you sent as an attachment and when I change the filters, the comments are always staying the same, they aren't updating with the new filter criteria. The comments are supposed to change along with the information in the cells of column Q when the filters are being changed.

Was it working for you on your computer? Is this a matter of me not having a specific script "allowed"? All I did was download your file, open it, and enabled editing to make sure i could see everything.

thanks

[Andy Pope]

If I change the values in B3:B9 on the Statistics worksheet then the comments update