Hyperlinking cells

**dschmitt** · 04-12-2010, 09:52 PM

Please have a look at the below sub. I have created 2 such subs for different sheets. One of the 2 is working fine. But for the other I get the error message in Excel that the "Reference is not valid".

I douple checked the code and looked at the actual hyperlinks that are created (by moving the cursor over the author with the hyperlink). Everything looks OK.

Does anybody know why I get this error message in Excel?

Please Login or Register  to view this content.

**vshankaa** · 04-13-2010, 12:32 AM

You are creating a hyerlink to a cell in a sheet named "J-Affiliation", and if you don't have that sheet in your workbook then you'd get that error message.

And you forgot to add a apostrophe ( ' ) to J-Affiliation.

Please Login or Register  to view this content.

**dschmitt** · 04-13-2010, 01:20 AM

indeed it was the missing apostrophe that caused the problem. I had them missing in both of my subs.

Strange that it worked in one but not in the other. I added the apostrophe to both subs.

Thanks.

**DonkeyOte** · 04-13-2010, 03:40 AM

Originally Posted by dschmitt

Strange that it worked in one but not in the other. I added the apostrophe to both subs.

The apostrophe would technically only be required as and when the sheet name warranted it - if the sheet was say "JJournals" you wouldn't need it whereas the use of hyphen in "J-Journals" necessitates it.

It's generally a good idea to always encase sheet names within apostrophes warranted or not, if not necessary XL will discard accordingly.