Circular reference error

[fitzsimons]

I have a workbook where all the data is on the first sheet labeled All Positions, the remaining sheets are a break down of this data.
The remaining sheets have formulas that will pull the data in from the main page when a date is entered in the cell and then I edit the formulas to add the hyperlink to the file.
Here is one of the formulas from the "Utility Person" page linking to the All positions page

"=IF('ALL POSITIONS'!F5>0,HYPERLINK("F:\DATA\WORD\How to Instructions\Employees DB\Aaroen, Laury\MSDS.pdf",'ALL POSITIONS'!F5))"

My problem is that I also need to hyperlink the cell from the main page to the cell it links to on the other page so the user can just click on the date and it jumps to the cell on the other page. Unfortuantely this creates a circular reference.
Is there a way to make this work besides using the excel hyperlink feature? Columns and rows are constantly being added and I am getting tired of rewritting formulas.

Let me know if I need to explain a little better.

Thanks!
Lisa

 

[jasonb75]

The problem with that formula is that when you enter it in there is nothing left in F5 for the formula to read.

Is the date in F5 before you add the hyperlink being typed manually or is it the result of a formula?

 

[jasonb75]

Assuming that you enter your dates manually, try

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
        Application.DisplayAlerts = False
Dim linkAdr As Range
   
If Not Intersect(Target, Range("F:F")) Is Nothing Then
    Set linkAdr = Application.InputBox("Please select hyperlink target cell", Type:=8)
        ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:=linkAdr.Address
End If
           Application.DisplayAlerts = True
End Sub

Copy the code to the worksheet module for the All Positions sheet, whenever you type something into a cell in column F it will ask you to select a target cell and add the link.

 

[fitzsimons]

So I was finally able to try the code provided above. I typed in the date in column F on the All positions worksheet, it would then pop up with the message box and I would point it to the Utility person worksheet, cell F5, then clicked ok. I then went back to the All positions worksheet and clicked on the date I just typed to see if the link to the Utility person page works but it doesn't do anything. The mouse-over box says that it is linked to itself.

 

[jasonb75]

Found some code that works

http://www.pcreview.co.uk/forums/hyperlink-macro-personal-xls-t2279705.html

Made a slight edit that should cater for your needs

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    
    Dim actual As Range
    Dim Act_Cell As Range
If Not Intersect(Target, Range("F:F")) Is Nothing Then
    Set Act_Cell = Target
    Set actual = Application.InputBox( _
    Prompt:="Select cell to hyperlink.", Type:=8)
        
    ActiveSheet.Hyperlinks.Add Anchor:=Act_Cell, Address:="", _
SubAddress:="'" & actual.Parent.Name & "'!" & actual.Address(0, 0), _
TextToDisplay:=actual.Parent.Name & "!" & actual.Address(0, 0)
End If
Application.EnableEvents = True
End Sub