Managing links to Summary from constantly changing worksheet names

[Guest]

Hi,

After developing a macro to create worksheets based on inputted employee
names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update
links to these constantly changing 200+ spreadsheets (6th sheet and on).
These links are contained on the same row where the user has entered in a new
employee name. Since the name of the new worksheet is not known at time of
development of spreadsheet, the links (e.g. on the 196th row) should refer to
the new spreadsheet (e.g. entered in C196).

Any help would be immensely appreciated!
Thanks in advance,
Kent Lysell

P.S. Here is my code that propagates the names in C10:C408 to their
respective worsheets and creates hyperlinks to each of the newly generated
sheets:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet
ActiveSheet.Unprotect Password:="1111"
Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp)
Set Rng = ws.Range("c10", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("Master").Visible = True
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value

cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
ActiveSheet.Protect Password:="1111"
Sheets("Master").Visible = False
End If
End If
Next
Application.Goto Reference:="Summary"

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="1111"
Next ws
End Sub

 

[Dave Peterson]

Just looking at your code...

Doesn't this line do what you want?

cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value

It adds the hyperlink to the cell in column C and points to the new worksheet
(cell A1)?

What am I missing?

 

[Guest]

Hi Dave,

Yes, the hyperlinks do work well. But there are still references in the same
row as where the user entered the new employee name on the Summary sheet that
capture date from specific cells in the newly created worksheet. I can e-mail
you the spreadsheet if you wish... I'm amazed at you guys for helping out the
Excel global community! I'd buy you guys a beer if you were in Ottawa
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Dave Peterson]

No thanks to sending me the workbook--I wouldn't open it anyway.

But if you have other hyperlinks in the same row that need to be adjusted, you
can look through those cells for hyperlinks--and just delete those hyperlinks
and add them back the way you want.

But it's difficult to guess what you really mean.

====
A nice thing about using the newsgroups is that there are lots of people who can
help.

 

[Guest]

Hi Dave,

No these links aren't hyperlinks, they're regular cell references in the
same row as where the user typed in a new Employee name, which was
subsequently used to create a new worksheet using the inputted employee name
via a macro. The problem is that I don't know the worksheet name beforehand
as they are created by the user subsequent to me developing this spreadsheet.
I realize that this might be a bit too complicated. I wanted users to already
have the linked cells set up when they added a new name to the range C10:C408
and then ran the macro to create the new worksheet with this employee name.
These worksheets in turn must have cells that link back to the Summary sheet.
Any Excel wizards know the answer?
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

 

[Dave Peterson]

You could use a formula that refers to that value in C##.

For instance, this will use the sheet name in C123 and retrieve the value from
x44.

=IF(ISERROR(CELL("address",INDIRECT("'"&C123&"'!a1"))),"missing",
INDIRECT("'"&C123&"'!x44"))

You could change "missing" to "" if you wanted to make the cell look empty.

But this is just a guess. You've never shared what the formulas actually look
like.

Hi Dave,

No these links aren't hyperlinks, they're regular cell references in the
same row as where the user typed in a new Employee name, which was
subsequently used to create a new worksheet using the inputted employee name
via a macro. The problem is that I don't know the worksheet name beforehand
as they are created by the user subsequent to me developing this spreadsheet.
I realize that this might be a bit too complicated. I wanted users to already
have the linked cells set up when they added a new name to the range C10:C408
and then ran the macro to create the new worksheet with this employee name.
These worksheets in turn must have cells that link back to the Summary sheet.
Any Excel wizards know the answer?

 

[Guest]

Wow! It works!!! Thanks Dave. I owe you one. Cheers, Kent.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211