Passing Variables into a Link

[Alec]

I have written VB code that, on the press of a pushbutton,
creates new sheets from a template, it also puts the sheet
name in cell A1 of the new sheet.
I also have a Summary Sheet, and when I press the add
sheet pushbutton a Range is copied from a list on another
sheet and inserted on the Summary Sheet with the new sheet
name included, also included in this newly pasted range,
is a link to the new sheet.
My query is, is it possible to use the new sheet name as a
variable and use it in the newly created link, for
example:-

A cell eg "'Summary Sheet'!F2" contains the text "Panel 1"
The link in "'Summary Sheet'!F3" is "='Panel 1'!C2"

I want to replace the text "Panel 1" in the link, with a
variable that will reference the contents of "'Summary
Sheet'!F2.

I have managed to do this in VB, but the code can get
messy as the number of sheets increase.
I have also tried other methods of inserting a variable in
a link, including the ADDRESS and CELL functions, but with
no success.

I would be grateful for any suggestions.

 

[Jan Karel Pieterse]

Hi,

If the sheetname is in cell A1, this formula creates a
hyperlink to cell C2 on that sheet:

=HYPERLINK("#'" & A1 & "'!C2";"'" & A1 & "'!C2")

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Alec]

Jan

Thanks for you reply;

However, its not a hyperlink I require, I do not want to
jump to the sheet I've just created, but to extract
information from it automatically, "'Summary Sheet'!F2"
contains the variable "Panel 1" and "'Summary Sheet'!F3"
is the cell in which I require contents from "='Panel 1'!
C2".

Alec

 

[Jan Karel Pieterse]

Hi,

Ah. OK.

Then use the INDIRECT worksheet function:

=INDIRECT("'" & A1 & "'!C2")

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Alec]

Jan

Works a treat.

Thanks
Alec

-----Original Message-----
Hi,

Ah. OK.

Then use the INDIRECT worksheet function:

=INDIRECT("'" & A1 & "'!C2")

Regards,

Jan Karel Pieterse
Excel TA/MVP

.