again, thank you very much, it works perfectly.

for the options that return numbers in a referenced range, i now get:

sf(d4) -- MAX(N62:N$66)+N$65
sf1(d4) -- MAX(N62:N$66)+3.14159265358979
sf2(d4) -- MAX(N62:N$66)+3.142
sf3(d4) -- MAX(0.999999, 4, 6.2831852, 3.14159265358979, 0.222222)+3.142
sf4(d4) -- MAX(1, 4, 6.283, 3.142, 0.222)+3.142

Excel should include these options in their functions...sf1 to 4 could be a
sf(d4,1) to ,4) type of function. i dare not ask you to create this option
for me, you've already done too much!

"Ron Rosenfeld" wrote:

On Thu, 3 Jan 2008 08:57:00 -0800, lawson
wrote:

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 the range such that it reads 'max(5, 22, 1, 33)' ?

As I wrote previously, I do not like using your #2 or #4 variants as the values
could be confused for range references (whole rows).

But here is a UDF that should resolve range references into a comma separated
list of values displayed in the cell (using the .Text property).

I ran two loops -- one recognizing and converting the single cell references;
and then a second loop which replaces the range references with a comma
separated list.

Some thing for you to be aware of that may or may not be an issue.

1. The .Text property can only return a maximum of 1024 characters from the
cell. If your cells might contain longer data, you will need to use the .Value
property.

2. If the target cell (the one reference by rg in the function) is returning
an error, changes in the references in that target cell will not be reflected
in this function unless you force a recalculation.

Let me know if this does what you need.

=================================================
Option Explicit
Function ShowFV(rg As Range)
'substitutes the contents of cell references for the references
'when doing ShowFormula
'BUT, it does NOT recognize RANGES, so will leave range references unchanged
'Also, 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
Dim c As Range, rg2 As Range
Dim t() As String
Dim i As Long

'test for valid single cell reference
If rg.Count < 1 Then
ShowFV = 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
str = re.Replace(str, sRepl)
Loop

'This pattern recognizes ONLY range references
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:\$?\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)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c.Text
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
ShowFV = str
End Function

================================================== ======
--ron