• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink Cell with Sheet

P

premjeetgupta

Member
Hi Folks,

Here i have attached an excel sheet. Where in Link Summ Sheet 270+ link name mentioned. I want to insert 270+ sheet and rename with link name and then sheet should hyperlink with links summ sheet. I am doing it manually it is taking too much time. Can anybody help me to to do this task with any quicker way ?

Thanks,
 

Attachments

  • Collector_NX_10G_Link_Summ.xls
    40.5 KB · Views: 0
Hi Premjeet ,

The link names are too long ; can we not use the link numbers in column A to name the individual tabs ?

The hyperlink on the Summary sheet can be associated with the link name , but clicking on the hyperlink will take you to the tab which is named after the link number e.g. clicking on the text Pune-Solapur will take you to the tab named 17.

Narayan
 
Sure Sir,

I have renamed the all sheet by doing some copied macro. But unable to find Hyperlink macro which is based on cell reference.

Thanks for the response sir.
 

Attachments

  • Collector_NX_10G_Link_Summ.xls
    832.5 KB · Views: 0
Thanks Sir,

You have done what i was looking. But one more small query. Can we add one back button option in every sheet. Because If any one goes sheet 70 and want to back on Index sheet he will have to do numerous time page up and page down.

Thank you very much for you kind support.

You are always rocks..!
 
Hi Premjeet ,

Can we add a button to the QAT , so that adding a button and taking up space on each sheet is avoided ?

Narayan
 
Hi Narayan Sir,

Just need some information about button option you have added in sheet. I have copied the macro you have written for for "back" option but the same code i am trying to add in another worksheet it is showing in macro option but button not highlighting on tool bar.
 
Hi Premjeet ,

The QAT is available only from Excel 2007 onwards , which is why I have uploaded a .xlsm file instead of the .xls file which you initially uploaded.

The backwards facing blue colored arrow on the QAT is linked to the GoBack macro.

Inserting a button on 270 sheets is a waste of space on the sheet , and more inconvenient for the user , since the user always has to return to the button to click. Once the button is available on the QAT , no sheet screen space is used up , and the button is always available , which ever tab you may be on.

Narayan
 
Hi Premjeet ,

No problem ; click on the Office button in the top left hand corner , and click on Excel Options.

Click on Quick Access Toolbar ; in the left hand dropdown labelled Choose commands from , click on Macros.

In the right hand dropdown labelled Customize Quick Access Toolbar , select the workbook where you want this button set up ; the default selection is :

For all documents ( default )

The list of macros available in the workbook will be displayed in the list on the left ; select GoBack , and click on the Add button.

Now , you can click on the Modify button below the list on the right , and select the symbol you wish to be used for the button.

Narayan
 
You must log in or register to reply here.
Back
Top