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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows
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:

1599113707790.png


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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows
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:

1599122373029.png


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
Joined
May 2, 2008
Messages
35,844
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In the actual workbook, are those formula cells formatted as Text?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
35,844
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
35,844
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,806
Office Version
  1. 365
Platform
  1. Windows
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. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,167
Messages
5,576,493
Members
412,729
Latest member
JanetCat73
Top