Function min_to_here() As Variant
Application.Volatile
Dim s As String
min_to_here = Sheets(1).Range("A1").Value
s = Application.Caller.Parent.Name
For i = 2 To Sheets.Count
If Sheets(i).Name = s Then
Exit Function
End If
If Sheets(i).Range("A1").Value < min_to_here Then
min_to_here = Sheets(i).Range("A1").Value
End If
Next
End Function

We get the name of the sheet on which the function is located. We then
examine the A1's on the sheets up to that sheet.
--
Gary''s Student - gsnu2007b


" wrote:

Thanks in advance to anyone that can help me here.

I have a workbook that will have a varying amount of sheets with
different names. The range in # of sheets will be 2-10. What I need
to do is use the formula =MIN(Sheet1:Sheet2!A1) where A1 is the cell
in each of the sheets prior to the one I am on, so that I can get the
minimum from the that cell value for each of the sheets. The problem
here is that I want the Sheet1:Sheet2 to not be static, but work based
on values in one or two cells (however it needs to work). I have
formulas that can give me the 1st sheet name and the next to last
sheet name perfectly (the last sheet will be the one for which I will
be running the Min formulas, etc).

Can this be done? Even if I was only able to build a formula that
could pull the information from the sheets preceding the one I was on
(the active one), that would work too.

Let me know if you need more information. Help is greatly
appreciated.

Conor