=Sheet2!A2 alternative

tezza

Active Member
Joined
Sep 10, 2006
Messages
375
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
  2. Web
Hi all, please put me out of my misery.

When I put =Sheet2!A2 into a call I get the value from Sheet 2 Cell A2 - great!

How do I change the Sheet2 part to Sheet (read the number from another cell say B1) - eg =sheet'B1'!A2 to get the same info as above?

I've tried indirect, brackets, &'s but not getting what I need.

This time I'm using office 365.

The reason behind it is I've put a little =sheet() formula in each sheet so users can simply use the sheet number rather than having to type in the tab name each time, plus it then reads 5 sheets across - twice but that's by-the-by.

Regards
Tezza
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You need to use INDIRECT when building a range reference dynamically, i.e.
Excel Formula:
=INDIRECT("Sheet" & B1 & "!A2")
 
Upvote 0
Solution
Natively, you can't refer to a worksheet by sheet number in a formula. You can create a UDF function in VBA to use in a formula though.
 
Upvote 0
The reason behind it is I've put a little =sheet() formula in each sheet so users can simply use the sheet number
Unfortunately that does not help, the formula will need the sheet name & not it's index number.
 
Upvote 0
You need to use INDIRECT when building a range reference dynamically, i.e.
Excel Formula:
=INDIRECT("Sheet" & B1 & "!A2")
Back to school for me to learn the correct way of using =INDIRECT lol, thank you.
 
Upvote 0
That will only work if all your sheets are called Sheet1, Sheet2 etc AND the order of those sheets is never changed.
It is a very risky way of doing things.
 
Upvote 0
Unfortunately that does not help, the formula will need the sheet name & not it's index number.
I'll get the user to put the sheet number in a cell (say B1) then use the following to read data from the sheet:

=IF(INDIRECT("Sheet" & B1 & "!A2")="test","Match","No match")
 
Upvote 0
That will only work if all your sheets are called Sheet1, Sheet2 etc AND the order of those sheets is never changed.
It is a very risky way of doing things.
Yeah I did think that but I believe that once it's setup then nobody should have a need to mess with it. My Excel limitations can't think of another way.
 
Upvote 0
once it's setup then nobody should have a need to mess with it.
That doesn't mean they wont. At least putting the sheet number in the cell manually will be better than using the Sheets function.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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