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

JenniferMurphy

Well-known Member
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:

VBA 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?

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

rondeondo

Board Regular
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

Well-known Member
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:

RoryA

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

JenniferMurphy

Well-known Member

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

MrExcel MVP, Moderator
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

Well-known Member

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

MrExcel MVP, Moderator
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

Well-known Member
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

MrExcel MVP, Moderator
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.

Replies
5
Views
147
Replies
2
Views
103
Replies
7
Views
123
Replies
2
Views
308
Replies
6
Views
227