Passing Variables into a Link

  • Thread starter Thread starter Alec
  • Start date Start date
A

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.
 
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
 
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
 
Hi,

Ah. OK.

Then use the INDIRECT worksheet function:

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

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
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

.
Click to expand...
 
Back
Top