Identify link location
 

Hi,

I have a spreadsheet that has multiple links. Is there a
way to create a different sheet that will display the list
of links in the following format:

SheetName-Cell(Address)- Full Link(cell value)

thank you for your help

Identify link location
 

Are you talking about external links/references in cells?

If so, just loop through all your cells and see if they contain brackets
([ ]) which would indicate a reference to another workbook. You could then
put that information in another sheet

Dim sh as Worksheet
Dim rw as Long
Dim rng as Range
Dim cell as Range
rw = 1
for each sh in thisworkbook.worksheets
if sh.Name < "Report" then
set rng = Nothing
on Error Resume Next
set rng = sh.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
for each cell in rng
if instr(cell.Formula,"[") then
worksheets("Report1").Cells(rw,1).Value = _
sh.Name & " - " & cell.Address & " - " _
& cell.Formula
rw = rw + 1
End If
Next
End if
End if
Next

--
Regards,
Tom Ogilvy


"igor" wrote in message
...
Hi,

I have a spreadsheet that has multiple links. Is there a
way to create a different sheet that will display the list
of links in the following format:

SheetName-Cell(Address)- Full Link(cell value)

thank you for your help

Identify link location
 

Tom,

You are the best.

Thank you
-----Original Message-----
Are you talking about external links/references in cells?

If so, just loop through all your cells and see if they
contain brackets
([ ]) which would indicate a reference to another
workbook. You could then
put that information in another sheet

Dim sh as Worksheet
Dim rw as Long
Dim rng as Range
Dim cell as Range
rw = 1
for each sh in thisworkbook.worksheets
if sh.Name < "Report" then
set rng = Nothing
on Error Resume Next
set rng = sh.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
for each cell in rng
if instr(cell.Formula,"[") then
worksheets("Report1").Cells(rw,1).Value
= _
sh.Name & " - " & cell.Address & " - "
_
& cell.Formula
rw = rw + 1
End If
Next
End if
End if
Next

--
Regards,
Tom Ogilvy


"igor" wrote in message
...
Hi,

I have a spreadsheet that has multiple links. Is there a
way to create a different sheet that will display the
list
of links in the following format:

SheetName-Cell(Address)- Full Link(cell value)

thank you for your help


.