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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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 :hungry: and I gotta say, some of the formulas y'all cook up (especially Mr. Akyurek) are downright scary sometimes. :o

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 Question
Excel Table - How to Append to Access Database Using VBA
Replies
1
Views
161
Micron
M
M
  • Solved
Locking down an excel sheet.
2
Replies
14
Views
480
MasterBash
M
L
  • Question Question
Macro-enabled workbook - Issue with co-authoring/sharing via user level access
Replies
1
Views
164
Lugen
L
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
173
druck21
D
B
  • Question Question
Applying values to a database based on multiple criteria sets
Replies
1
Views
107
Baldred
B

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

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