how to access a sheet by only knowing its position

S

s95a1li

New Member
Joined
Jan 12, 2004
Messages
2
To access a cell A1 in the "Sheet2" sheet I simply use =Sheet2!A1

How can I access the second sheet in the workbook without referring to its name? (It will be different from time to time)

In visual basic I can access the 2nd sheet in a workbook with
Sheets(2).Cell(1,1).Value

Is there a way to access a sheet the same way by just using excel.

Something like =[Sheet which has the second position in the workbook]!A1

Thanks a lot in advance!

Andy
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
"Is there a way to access a sheet the same way by just using excel. "

Not that I'm aware of. (although therre are no doubt a number of more or less elaborate workarounds, the details of which would depend on the details of what you're trying to do...)
 
Upvote 0
Hello Andy, use a UDF:

Function myRef(ByVal shtIndx As Long, ByVal trgRng As Range) As Variant
myRef = Sheets(shtIndx).Range(trgRng.Address).Value
End Function

=myref(2,A1)
 
Upvote 0
There is a way without using any VBA, but it's danged ugly. If you don't like Nate's which is much easier, let me know and I'll post it.
 
Upvote 0
Okey-doke :)

Create a named formula (in my case I named it wsNames):

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

then the formula to retrieve cell A1 in sheet 2 is:

=INDIRECT("'" &INDEX(wsNames,2) & "'!A1")

2nd arg of Index being the ordinal number of the sheet you want.
 
Upvote 0
Thanks for that tip and link, JPG. I just read that thread :p and I gotta say, some of the formulas y'all cook up (especially Mr. Akyurek) are downright scary sometimes. :eek:

And Jon, thank ya kindly for the compliment, but after seein' JPG's handiwork on that other thread, my hat-size ain't a goin' up too much.
 
Upvote 0
If you are just trying to navigate.

I create "Named Cells" in each sheet using the sheet name. No formulas, no VB, UDF's, no HyperLinks, no nothing. Pretty basic stuff.
When I want to go to a particular sheet, Just go to the cell ref window and click on the sheet name. Takes you right where you want to go.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Access data from a closed workbook
Replies
6
Views
126
Micron
M
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T
W
  • Question
Sheet that updates by referencing cells in workbooks located in separate folders on shared drive
Replies
0
Views
143
Woodpusher147
W
M
  • Question
Excel spreadsheet - online as a interactive website - what to do next
Replies
1
Views
97
Rijnsent
Rijnsent
S
  • Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
144
sschaefer
S

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top