Hyperlink function not jumping

[Mrock1]

I am very new to using the Hyperlink function and after resolving an issue when using nested IFs with different hyperlink functions in a cell I have found something odd. If the cell containing the hyperlink function isn’t selected, then clicking on the cell works just fine. However, if the cell has been selected (eg by using the cursor keys, clicking on the cell does nothing.

While I don’t think it’s related (I’m happy to be proven wrong), the cell also has a data validation list. If the cell is already selected, clicking on it not only ignores the hyperlink but it also drops down the data validation list as if I’d clicked on the dropdown arrow.

I have used hyperlinks on cells before using the Ctrl-K method and this works anytime the cell is clicked on, selected or not. I’m also aware that the hyperlink function allows a ping mouse click to select the cell, but that’s not what I’m doing.

Hope someone knows how to correct this behaviour.

Thx
Max

 

[Mrock1]

As an update I tried a simple workbook with a very simply conditional hyperlink function in a single cell with single IF condition looking for an ISBLANK in B1, then jumping to A1 if blank and B1 if not. That worked. I then added a data validation list (1,2,3,4) and that worked as well. I did find a workaround in my workbook by enhancing my already conditional data validation list to make it a single cell range if it was blank and that seemed to work. I think I've out complicated myself.

The cell contatins the formula:
=LET(Prev, MATCH($C576,$C$19:$C575,0),
PrevRecordFound, ISNUMBER(Prev),
ThisYr, DATE(YEAR(TODAY()),MONTH(T$19&"1"),1),
IsDue, MOD(DATEDIF([@[Start Date]],ThisYr,"M")/VLOOKUP(O576,MyPeriods,2,FALSE),1)=0,
ValueAtPrev, INDEX([Mar],Prev-1),
HPLAofPrev, "#" & ADDRESS(Prev+18,COLUMN()),
HPLAofCurr, "#" & ADDRESS(ROW(),COLUMN()),

IF(AND(PrevRecordFound,IsDue),
IF(ValueAtPrev="",
HYPERLINK(HPLAofPrev,"Click here to record at row " & Prev+18),
HYPERLINK(HPLAofPrev,ValueAtPrev)
),
""
)
)

Column C contains the record numbers, which are not unique and may be repeated. The IsDue calculation is over a 12 columns, one for each month, Jan-Dec, the header of which is at row 19. The MyPeriods contains a 2-column array of Monthly, Quarterly, Semi-Annually, Annually, Every 2-Years, Every 3-Years; 1, 3, 6, 12, 24, 36; where the second column if the number of months in the period.

The data validation (now) in the cell is a list defined as follows:

=IF(AND(ISERROR(MATCH($C576,$C$19:$C575,0)),MOD(DATEDIF($N576,DATE(YEAR(NOW()),MONTH(T$19&"1"),1),"M")/VLOOKUP($O576,$N$2:$O$7,2,FALSE),1)=0,DATE(YEAR(TODAY()),MONTH(T$19&"1"),1)>=$N576),$W$2:$W$8,$X$9)

So it works now, but did't work until I added the condition, ISERROR(MATCH($C576,$C$19:$C575,0)), to the above dat validation formula. which essentially sets the validation list to $X$9 if the record number stored in $C576 isn't found in the rows, $C$19:C$575. I really can't figure out why it didn't work beofre, but it does now.

I can only say, all's well that ends well, but it was quite strange that it wouldn't follow the hyperlink function when the cell was already selected, only if it was clicked while in any other cell.

Thanks if you considered my issue anyway.

Max