Changing cell reference from relative to absolute causes result to be a Mailto: link???

[JenniferMurphy]

I have no idea what's happening here. I am hoping someone can enlighten me.

I am working on a workbook to help me keep track on insulin injections. Part of it is generating a rotating schedule of finger-priick (*) locations for blood glucose readings.

* I had to spell it that way to keep it from getting bleeped

I came up with a schedule based on clock positions so that any particular site is only used once every 12 days. The site on each finger will cycle around the clock positions by 5s: 1 - 6 - 11 - 4 - 9 - 2 - 7 - 12 - 5 - 10 - 3 - 8 - 1 ... and the fingers will cycle from 1 - 2 - 3 - 4 - 5 - 1...

I wrote a little UDF that will generate a repeating cycle of fingers and sites. Here's that code:

Function FingerLoc(pLast As String) As String
Dim parts
Dim finger
Dim clock
parts = Split(pLast, "-")
finger = (parts(0) Mod 5) + 1
If finger = 1 Then
  clock = ((parts(1) + 4) Mod 12) + 1
Else
  clock = parts(1)
End If
FingerLoc = finger & "-" & clock
End Function

The code has worked perfectly for months. But today, I tried changing the address of the cell containing the previous site from relative to half absolute and it blew up. Here's some sample output:

C4 contains a literal for the starting site ("3-5" means 5 o'clock on finger #3). C5-C9 contain the sequence of sites that are the results of the UDF. C10 should do the same, but when I change the cell reference from C15 to $C15, the result is some weird link. If I hover over it, I get a pop-up that says something about "mailto:". If I click on it, it opens up an Outlook email addressed to that link.

What the heck is going on?

 

[rondeondo]

Hi Jennifer

in yous screen shot you are in cell D10 but your formula relates to C15, is that intentional? I've tried putting in the same as you and get a #Value error with the reference to later in the sheet. If you make the cell ref correct for the row it's on it works for me

 

[JenniferMurphy]

Hi Jennifer

in yous screen shot you are in cell D10 but your formula relates to C15, is that intentional? I've tried putting in the same as you and get a #Value error with the reference to later in the sheet.

No that was my fat fingers. Here's the correct table:

If you make the cell ref correct for the row it's on it works for me

Are you saying that the same formulas as above, including C9 & C10, work for you? What can be different in my workbook?

I've uploaded the workbook to this Dropbox folder:

20200903 Mailto error

Shared with Dropbox

www.dropbox.com

 

[RoryA]

In the actual workbook, are those formula cells formatted as Text?

 

[JenniferMurphy]

In the actual workbook, are those formula cells formatted as Text?

Yep, all of them. I also tried formatting them as General. Same result.

 

[RoryA]

If they are formatted as text, as soon as you edit and re-enter them they will convert to hyperlinks because they are viewed as text strings containing an @ symbol - it's not actually anything to do with adding the $ sign.

 

[JenniferMurphy]

If they are formatted as text, as soon as you edit and re-enter them they will convert to hyperlinks because they are viewed as text strings containing an @ symbol - it's not actually anything to do with adding the $ sign.

Wow! You are right. Thanks. I guess the geniuses at M$FT didn't think the whole @ sign decision through completely. (sigh)

 

[RoryA]

You don't actually need the @ there as far as I can see. Also, you can turn off the automatic conversion in the autocorrect options.

 

[JenniferMurphy]

You don't actually need the @ there as far as I can see. Also, you can turn off the automatic conversion in the autocorrect options.

OK, thanks

 

[Jon von der Heyden]

The behaviour that you describe is a feature, rather than a bug or an oversight (indeed a feature I have benefited from many times in the past). You need to consider data types (and understand them, if you do not already) during your workbook design and build, and not necessarily expect one data type to behave as another. Glad that your issue is resolved now.