Automatic updating of Hyperlink to a variable sheetname

[aerodan]

Hi all, new to the forum. Already learned a lot. I'v tried scouring through existing posts to find something like this problem of mine. Hoping someone can help...

Basically I have a workbook made up of the following:
A Summary sheet and a number of user populated forms.

A macro "CreateNewForm", creates a new worksheet (copied from a blank form) that a user then populates. The form contains an embedded macro that changes the sheet name automatically when you change the value of Cell(Q6).

The "CreateNewForm" macro also inserts a new row to the Summary sheet with the data from the newly created form. In the Summary sheet, Cell(B11) is linked to the new sheet, initially via =new!Q6

Then, as you change the name of the new sheet to any other string, eg. 'A 01 01' , the Summary sheet also updates Cell(B11) to ='A 01 01'!Q6.

So far so good.

Now I want to hyperlink Cell(B11) in the Summary to the the new sheet. However the sheet name can change depending on the user.

I can make my "CreateNewForm" macro to add a hyperlink to Cell(B11) on the Summary sheet however I don't know how to get the hyperlink (address) to update as the user changes the name of the destination sheet.

Is this even possible? Any help is approeciated.
Many thanks for your time!

 

[Andrew Poulsom]

Welcome to MrExcel.

Name the destination cell using Insert|Name|Define and hyperlink to that name.

 

[aerodan]

Thanks for the reply Andrew. Not sure I understand though. The destination cell is any cell on a sheet whose sheetname is variable. Are you saying I need to Insert|Name|Define the sheet? Can this be automated as each new form is created?

 

[Andrew Poulsom]

How can you have a hyperlink to "any cell". Try using the HYPERLINK function:

=HYPERLINK("#"&new!Q6,"Click me")