Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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

Sheet Numbers Q
 

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