Hi Wes,

You could try this:

1) menu InsertNameDefine...
2) write SHEETARRAY in the name box, and
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,256+NOW())
in the 'Refers to:' box. Press 'Add' and then 'OK'.
3) now you can refer to the sheets by their number using for example the
following formula:
=INDIRECT("'"&INDEX(SHEETARRAY,3)&"'!B1")

Notes:

1.This method is not recommended for XL97 or 2000 as according to Harlan
Grove the XLM functions used this way may shut down the Excel with the loss
of unsaved data ( http://tinyurl.com/49oqa )

2. If a new sheet is inserted, it will not appear in the SHEETARRAY until
the formulae are next recalculated.

Regards.
KL


"Wes" wrote in message
...
Thanks Harlan, but unfortunately I'm trying hard to do this without any
VBA.
Having coding in this spreadsheet becomes a pain because it is used
throught
a school on many different computers which I would then have to lower
security to and then install a certificate on.



"Harlan Grove" wrote:

Bob Phillips wrote...
No as easily.

You could use

=INDIRECT("Sheet"&A1&"!C5")

as an example, as long as they all follow the Sheet1, Sheet2, ...
format.
....

Another option would be user-defined functions, e.g.,


Function ref(wsr As Variant, rr As String) As Range
Dim wb As Workbook, ws As Worksheet

Set wb = Application.Caller.Parent.Parent

If TypeOf wsr Is Range Then wsr = wsr.Cells(1, 1).Value2

If VarType(wsr) = vbDouble Then
wsr = Int(wsr)

If 1 <= wsr And wsr <= wb.Worksheets.Count Then
Set ref = wb.Worksheets(wsr).Range(rr)
End If

ElseIf VarType(wsr) = vbString Then
On Error Resume Next
Set ws = Evaluate("'" & wsr & "'!A1").Parent

If Not ws Is Nothing Then
Set ref = ws.Range(rr)

Else
Err.Clear

For Each ws In wb.Worksheets
If ws.CodeName = wsr Then Set ref = ws.Range(rr)
Next ws

End If

End If

End Function


Off on a tangent: I was testing this udf with the formula

A2:
=CELL("Address",ref(A1,"A5"))

in a new workbook. I'd just like to point out that CELL called with 1st
arg "Address" and second arg referring to a cell in a different
worksheet happily includes the name of the unsaved workbook, but called
with 1st argument "Filename" it returns "". Obviously it isn't
impossible to return the dummy filename for unsaved files.

Microsoft copied Lotus 123 Release 2's @CELL as the basis for Excel's
CELL function. Since 123 Release 3.0 (Summer 1989), 123's @CELL
function returns a dummy but nonblank workbook name when called with
"Filename" as 1st arg. I know it's imprudent to expect Microsoft to
bring bits & pieces of Excel up to the level of functionality that its
erstwhile competitors achieved more than a decade and half ago, but
it's still nice to dream about. And with no effective competition
anymore, ridicule is the only means left to influence Microsoft.