Passing Variables into Links

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.

=indirect("'" & 'Summary Sheet'!F2 & "'!C2")

--
Regards,
Tom Ogilvy

Alec wrote in message
...
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.

Thanks Tom, it works a treat.

Best Regards
Alec

-----Original Message-----
=indirect("'" & 'Summary Sheet'!F2 & "'!C2")

--
Regards,
Tom Ogilvy

Alec wrote in message
...
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.



.