Referencing Worksheets from Cell Values

[conorfinnegan]

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

 

[Gary''s Student]

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.

 

[Jim Thomlinson]

No need to get too fancy here. The formula that you have will look at Sheet1
and Sheet2 and all sheets in between. So so long as any new sheets are added
inbetween the sheets listed in the fromula you are good to go. To that end
you want to add a couple of Dummy sheets to your workbook. Call them Start
and End (or whatever) Place them ahead of sheet 1 and after sheet2. Change
your formula to
=MIN(Start:End!A1)
You may need to spike the values in start and end so that they are not the
minimums. Now hide the Start and End sheets. When new sheets are added they
should land between start and end and since start and end are hidden you do
not have to worry about users moving them (generateing wrong answers) or
deleteing them (invalidating the formulas).