Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sean" wrote in message
...
Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FSt1" wrote in message
...
hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

But surely that won't work if the sheet name is changed?
--
David Biddulph

"FSt1" wrote in message
...
hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

On Feb 17, 2:33*pm, "Don Guillett" wrote:
Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

That did the trick, Thanks

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sean" wrote in message
...
On Feb 17, 2:33 pm, "Don Guillett" wrote:
Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

That did the trick, Thanks

hi
you're right. but all my formula is is a re-write of the formula he tried,
ment only as an example. he could re-write to fit.

regards
FSt1

"Don Guillett" wrote:

But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FSt1" wrote in message
...
hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

hi,
you're right. but he used sheet1 in his formula. i assumed his sheet name
were numbered.

Regards
FSt1

"David Biddulph" wrote:

But surely that won't work if the sheet name is changed?
--
David Biddulph

"FSt1" wrote in message
...
hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

Only if using VBA.


Gord Dibben MS Excel MVP

On Sun, 17 Feb 2008 05:08:27 -0800 (PST), Sean wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

FSt1

OP wants to use the codename of the sheet, not the name of the sheet.

Run this to see the difference.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next i
End Sub


Gord Dibben MS Excel MVP


On Sun, 17 Feb 2008 09:51:01 -0800, FSt1 wrote:

hi,
you're right. but he used sheet1 in his formula. i assumed his sheet name
were numbered.

Regards
FSt1

"David Biddulph" wrote:

But surely that won't work if the sheet name is changed?
--
David Biddulph

"FSt1" wrote in message
...
hi
you were so close.
try this....
=sheet1!A1

regards
FSt1

"Sean" wrote:

Is it possible to reference a cell by its worksheet number, rather
than its sheet name?

For e.g. Sheet1 A1 as "=worksheet(1)a1

I've tried above and it doesn't work. Reason I ask is that I have
sheets that are added and change each week. But the sheet I want to
reference will always be the "2nd" sheet from left in my workbook

Thanks

On Feb 17, 3:27*pm, Sean wrote:
On Feb 17, 2:33*pm, "Don Guillett" wrote:

Put this in a REGULAR module and then use as a formula
=sht2("a1")

Function sht2(x)
Application.Volatile
sht2 = Sheets(2).Range(x)
End Function

That did the trick, Thanks

It seems under certain conditions, the above returns #Value! and when
I edit the cell and Return, the correct value appears again. Not
really what the circumstances are. Why is this?

It maybe because I extract two sheets ("Summary Report" within which
my formula =sht2("a1") appears) and the 3rd sheet. Thus I have only
effectively 2 sheets now in the workbook where my formula runs. See
code below for sheet extraction. It is only after these I change all
cells to values. Is there any way around this?

Set Sourcewb = ActiveWorkbook

i = Worksheets("Summary Report").Index
wsVar = Worksheets(i + 2).Name
Sourcewb.Sheets(Array("Summary Report", wsVar)).Copy
Set Destwb = ActiveWorkbook

For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh