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

 

[mole999]

can you reference the cell close by, so its not circular, and then use that to do your hyperlink

 

[fitzsimons]

I thought about that but the users of this spreadsheet seem to get confused easily so I have a macro set up so when they click a cell it highlights, and when they click on the hyperlinked date it jumps to the linked cell and highlights it. That way they don't lose their place. (and I don't have to keep hearing "where'd it go")

Lisa

 

[jasonb75]

I'm not sure what you're trying to explain with the references you're using, the formula you gave as an example is linking to another file, not another cell.

What's in All positions F5?

 

[fitzsimons]

All positions cell F5 contains a date of 8/2/01.
This formula:
"=IF('ALL POSITIONS'!F5>0,HYPERLINK("F:\DATA\WORD\How to Instructions\Employees DB\Aaroen, Laury\MSDS.pdf",'ALL POSITIONS'!F5))"

is on the Utility Person page. This formula pulls the 8/2/01 date into the F5 cell and also links to the file on the F drive.

 

[jasonb75]

Ok, so that makes sense now, the formula is in F5 on the utility person sheet?

But what I'm not getting is

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.

are you saying 'click on the date in F5 of ALL POSITIONS an it should go to F5 of Utility person?

 

[fitzsimons]

Yes that is right, clicking on the F5 date on the all positions shoult go to F5 of Utility person.

When I try to use a hyperlink formula it gives a circular reference.

 

[VoG]

Take a look at this event procedure

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'your code goes here
End Sub

 

[jasonb75]

What formula are you trying to use in All positions F5?

 

[fitzsimons]

I initially tried "=HYPERLINK('UTILITY PERSON'!F5,F5)" in cell F5 on the all positions page, but of course that is what gives the circular reference error.

I will see what I can do with the event procedure. I am still pretty new at all this so when my head starts to hurt I will be back asking for more help.