Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
location of a link in server | Excel Discussion (Misc queries) | |||
link not updating after changing source location | Excel Discussion (Misc queries) | |||
Please help: identify where an automatic link to other spreadsheet is? | Excel Worksheet Functions | |||
link to identify lastest entry in the row | Excel Discussion (Misc queries) | |||
How to identify source or location of MAX_VALUE in range | Excel Worksheet Functions |