Hi Ron,

Happy New Year!

i have 2 new requests for you

First, the sf4 that i have currently is not able to convert cell references
with a $ in them to a number value, whether it is in a range of cells or a
simple single cell reference

Second, and i understand that this could be a tall order, for sf4, which
converts ranges to read 'max(5:33)', is ti possible to show all the values
within th erange such that it reads 'max(5, 22, 1, 33)' ?

my sf4 is as follows, and below is the code you gave me that converted the $
to values in non-range references:

--------------
Function SF4(rg As Range) 'ShowFormula - MAX(0.999:0.222)+0.777
'substitutes the contents of cell references for the references when doing
ShowFormula
'does NOT recognize RANGES, so will only return the contents of the First
and Last cells in the range.
'does NOT recognize NAME'd references
'Requires A1 cell reference style

Dim str As String
Dim re As Object, mc As Object
Dim sRepl As String

'test for valid single cell reference
If rg.Count < 1 Then
SF4 = CVErr(xlErrRef)
Exit Function
End If

str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
re.Pattern = "\b([A-Z]|[A-I][A-V])([1-9]\d{0,3}|" & _
"[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6])\b"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = Range(mc(0)).Text '(the .text will cause it to read the
referenced cell as a text value, which will remove the decimal places)
str = re.Replace(str, sRepl)
Loop
SF4 = str
End Function


------- here is the one that converts d$44 to read 31.123
Function SF2(rg As Range) 'ShowFormula - MAX(L41:L43)+0.777 This one returns
the format (decimal places) from the original cell
'substitutes the contents of cell references for the references when doing
ShowFormula
'does NOT recognize RANGES, so will leave range references unchanged
'does NOT recognize NAME'd references
'Requires A1 cell reference style

Dim str As String
Dim re As Object, mc As Object
Dim sRepl As String

'test for valid single cell reference
If rg.Count < 1 Then
SF2 = CVErr(xlErrRef)
Exit Function
End If

str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:$]|^)\$?\b(([A-Z]|[A-I][A-V])\$?([1-9]\d{0,3}" & _
"|[1-5]\d{0,4}|6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!:)"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1)).Text '(the
..text will cause it to read the referenced cell as a text value, which will
remove the decimal places)
str = re.Replace(str, sRepl)
Loop
SF2 = str
End Function