reference to sheets without using sheet names

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

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.

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

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.

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

Thanks. Just checked under XL2000 and effectively it does crash.

KL


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "KL" <[email protected]> wrote...
>>>Test it. Use of XLM functions in defined names will crash Excel 97
>>>and 2000 (and probably also Excel 95).
>>
>>I have. Been using XLM functions in defined names for a few years
>>under XL97 and 2K and have never run into problems. But, as you could
>>see, I have mentioned your comment in my post.
>
> It happens when you copy cells containing references to such defined named
> then try to paste into other worksheets. It always crashes Excel.
>

"KL" <[email protected]> wrote...
>>Test it. Use of XLM functions in defined names will crash Excel 97
>>and 2000 (and probably also Excel 95).
>
>I have. Been using XLM functions in defined names for a few years
>under XL97 and 2K and have never run into problems. But, as you could
>see, I have mentioned your comment in my post.

It happens when you copy cells containing references to such defined named
then try to paste into other worksheets. It always crashes Excel.

> Test it. Use of XLM functions in defined names will crash Excel 97 and
> 2000 (and probably also Excel 95).

I have. Been using XLM functions in defined names for a few years under XL97
and 2K and have never run into problems. But, as you could see, I have
mentioned your comment in my post.

> If the OP's really in a school environment, then the odds are high (at
> least in the US) that there's more than one version of each application in
> use.

Maybe - you know better, I am not in the US :-) That's why I thought
appropriate to quote you in my post.

> That said, it's difficult to see why such functionality would be needed in
> worksheets that aren't static in design, i.e., referring to worksheets by
> index number doesn't make sense in spreadsheet apps in which users could
> insert or delete arbitrary worksheets (and thus fubar worksheet
> references). If workbooks would be static/unchanging in terms of worksheet
> number and order, it'd be safer to enter a list of worksheets in a range
> in one of the worksheets (or use a new worksheet just to hold such a
> list), name that range SheetArray (tangent: always better to use mixed
> case for defined names and UDFs to make it immediately obvious they differ
> from built-in functions), and use it instead.

agree.

KL

"Wes" <[email protected]> wrote...
>Thanks!
>
>"KL" wrote:
>>You could try this:
>>
>>1) menu Insert>Name>Define...
>>2) write SHEETARRAY in the name box, and
>>=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,256+NOW())
....
>>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 )
....

Test it. Use of XLM functions in defined names will crash Excel 97 and 2000
(and probably also Excel 95). If the OP's really in a school environment,
then the odds are high (at least in the US) that there's more than one
version of each application in use.

That said, it's difficult to see why such functionality would be needed in
worksheets that aren't static in design, i.e., referring to worksheets by
index number doesn't make sense in spreadsheet apps in which users could
insert or delete arbitrary worksheets (and thus fubar worksheet references).
If workbooks would be static/unchanging in terms of worksheet number and
order, it'd be safer to enter a list of worksheets in a range in one of the
worksheets (or use a new worksheet just to hold such a list), name that
range SheetArray (tangent: always better to use mixed case for defined names
and UDFs to make it immediately obvious they differ from built-in
functions), and use it instead.

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.
>
>

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

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.

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

Hi Wes,

You could try this:

1) menu Insert>Name>Define...
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>>
>>

Thanks!

"KL" wrote:

> Hi Wes,
>
> You could try this:
>
> 1) menu Insert>Name>Define...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >>
> >>
>
>
>

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

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.

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

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.

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

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.

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

Sorry I guess I didn't word my question clearly. I'm wanting to do this as a
worksheet function. Is that possible? I was hoping to make this spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

> Worksheets(Range("A1").Value).Range("somecellref").Value
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Just wondering if there is a way to reference a sheet without using its
> name.
> > I know in VBA you can reference the first, second, third etc. sheet or
> use
> > their names.
> >
> > I want to use a formula that references a certain sheet dependent on a
> > different cell value. For example: if cell a1 value is 3, then my formula
> > will refernce a cell on the third sheet. if cell a1 is 2, then the
> formula
> > would reference the same cell but on the second sheet.
> >
> > Thanks in advance for your help.
>
>
>

Just wondering if there is a way to reference a sheet without using its name.
I know in VBA you can reference the first, second, third etc. sheet or use
their names.

I want to use a formula that references a certain sheet dependent on a
different cell value. For example: if cell a1 value is 3, then my formula
will refernce a cell on the third sheet. if cell a1 is 2, then the formula
would reference the same cell but on the second sheet.

Thanks in advance for your help.

Worksheets(Range("A1").Value).Range("somecellref").Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Just wondering if there is a way to reference a sheet without using its
name.
> I know in VBA you can reference the first, second, third etc. sheet or
use
> their names.
>
> I want to use a formula that references a certain sheet dependent on a
> different cell value. For example: if cell a1 value is 3, then my formula
> will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
> would reference the same cell but on the second sheet.
>
> Thanks in advance for your help.

Thanks!

"KL" wrote:

> Hi Wes,
>
> You could try this:
>
> 1) menu Insert>Name>Define...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >>
> >>
>
>
>

No as easily.

You could use

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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" <[email protected]> wrote in message
news:[email protected]...
> Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
> worksheet function. Is that possible? I was hoping to make this
spreadsheet
> entirely without VBA.
>
> Thanks.
>
> "Bob Phillips" wrote:
>
> > Worksheets(Range("A1").Value).Range("somecellref").Value
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just wondering if there is a way to reference a sheet without using
its
> > name.
> > > I know in VBA you can reference the first, second, third etc. sheet
or
> > use
> > > their names.
> > >
> > > I want to use a formula that references a certain sheet dependent on a
> > > different cell value. For example: if cell a1 value is 3, then my
formula
> > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > formula
> > > would reference the same cell but on the second sheet.
> > >
> > > Thanks in advance for your help.
> >
> >
> >

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wes" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> as a
> > worksheet function. Is that possible? I was hoping to make this
> spreadsheet
> > entirely without VBA.
> >
> > Thanks.
> >
> > "Bob Phillips" wrote:
> >
> > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wes" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Just wondering if there is a way to reference a sheet without using
> its
> > > name.
> > > > I know in VBA you can reference the first, second, third etc. sheet
> or
> > > use
> > > > their names.
> > > >
> > > > I want to use a formula that references a certain sheet dependent on a
> > > > different cell value. For example: if cell a1 value is 3, then my
> formula
> > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > formula
> > > > would reference the same cell but on the second sheet.
> > > >
> > > > Thanks in advance for your help.
> > >
> > >
> > >
>
>
>

You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

> To bad there isn't an easy solution, since the problem I have is that they
> all have sheet names that have been changed, and may change again. That is
> what led me to this problem in the first place.
>
> Thanks for your help though.
>
> "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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Wes" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I guess I didn't word my question clearly. I'm wanting to do this
> > as a
> > > worksheet function. Is that possible? I was hoping to make this
> > spreadsheet
> > > entirely without VBA.
> > >
> > > Thanks.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Worksheets(Range("A1").Value).Range("somecellref").Value
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Wes" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Just wondering if there is a way to reference a sheet without using
> > its
> > > > name.
> > > > > I know in VBA you can reference the first, second, third etc. sheet
> > or
> > > > use
> > > > > their names.
> > > > >
> > > > > I want to use a formula that references a certain sheet dependent on a
> > > > > different cell value. For example: if cell a1 value is 3, then my
> > formula
> > > > > will refernce a cell on the third sheet. if cell a1 is 2, then the
> > > > formula
> > > > > would reference the same cell but on the second sheet.
> > > > >
> > > > > Thanks in advance for your help.
> > > >
> > > >
> > > >
> >
> >
> >

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.

Thanks. Just checked under XL2000 and effectively it does crash.

KL


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "KL" <[email protected]> wrote...
>>>Test it. Use of XLM functions in defined names will crash Excel 97
>>>and 2000 (and probably also Excel 95).
>>
>>I have. Been using XLM functions in defined names for a few years
>>under XL97 and 2K and have never run into problems. But, as you could
>>see, I have mentioned your comment in my post.
>
> It happens when you copy cells containing references to such defined named
> then try to paste into other worksheets. It always crashes Excel.
>

"KL" <[email protected]> wrote...
>>Test it. Use of XLM functions in defined names will crash Excel 97
>>and 2000 (and probably also Excel 95).
>
>I have. Been using XLM functions in defined names for a few years
>under XL97 and 2K and have never run into problems. But, as you could
>see, I have mentioned your comment in my post.

It happens when you copy cells containing references to such defined named
then try to paste into other worksheets. It always crashes Excel.

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.
>
>

> Test it. Use of XLM functions in defined names will crash Excel 97 and
> 2000 (and probably also Excel 95).

I have. Been using XLM functions in defined names for a few years under XL97
and 2K and have never run into problems. But, as you could see, I have
mentioned your comment in my post.

> If the OP's really in a school environment, then the odds are high (at
> least in the US) that there's more than one version of each application in
> use.

Maybe - you know better, I am not in the US :-) That's why I thought
appropriate to quote you in my post.

> That said, it's difficult to see why such functionality would be needed in
> worksheets that aren't static in design, i.e., referring to worksheets by
> index number doesn't make sense in spreadsheet apps in which users could
> insert or delete arbitrary worksheets (and thus fubar worksheet
> references). If workbooks would be static/unchanging in terms of worksheet
> number and order, it'd be safer to enter a list of worksheets in a range
> in one of the worksheets (or use a new worksheet just to hold such a
> list), name that range SheetArray (tangent: always better to use mixed
> case for defined names and UDFs to make it immediately obvious they differ
> from built-in functions), and use it instead.

agree.

KL

"Wes" <[email protected]> wrote...
>Thanks!
>
>"KL" wrote:
>>You could try this:
>>
>>1) menu Insert>Name>Define...
>>2) write SHEETARRAY in the name box, and
>>=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,256+NOW())
....
>>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 )
....

Test it. Use of XLM functions in defined names will crash Excel 97 and 2000
(and probably also Excel 95). If the OP's really in a school environment,
then the odds are high (at least in the US) that there's more than one
version of each application in use.

That said, it's difficult to see why such functionality would be needed in
worksheets that aren't static in design, i.e., referring to worksheets by
index number doesn't make sense in spreadsheet apps in which users could
insert or delete arbitrary worksheets (and thus fubar worksheet references).
If workbooks would be static/unchanging in terms of worksheet number and
order, it'd be safer to enter a list of worksheets in a range in one of the
worksheets (or use a new worksheet just to hold such a list), name that
range SheetArray (tangent: always better to use mixed case for defined names
and UDFs to make it immediately obvious they differ from built-in
functions), and use it instead.

Hi Wes,

You could try this:

1) menu Insert>Name>Define...
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>>
>>