# Sheet Refs

#### AlexN

##### New Member
Is there away to create a sheet reference inside a formula. For example I want to make cell C2 in sheet 1 = to cell C2 in sheet 2.

Can I make a formula that refers to the sheet name in cell B2 in sheet 1.

A B C D E
1
2 sheet 2 ='sheet 2'(C2)
3 sheet 3 ='sheet 3'(C2)
4 sheet 4 ='sheet 4'(C2)

so the formula in C2 I would like to make refer to the name in B2, if that makes any sense!

Cheers

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### abshaw

##### Board Regular

are all of the sheet names generic, that is sheet 1 sheet 2 sheet 3 and so on as you mentioned (if not then its a whole new ball game).

#### Smitty

##### Legend
Welcome to the Board!

Sure you can do what you're asking.

In Sheet1, Cell C2, Enter =, then CTRL+Page Down and select Sheet2, Cell C2, then hit enter.

The formula would be: =Sheet2!C2

Hope that helps, but if I'm misunderstanding, please post back!

Smitty

#### RichardS

##### Well-known Member
Do you mean like this?
Book2
ABCD
1
2Sheet2!100100
3Sheet3!300300
Sheet1

Richard

#### AlexN

##### New Member
Hi,

In answer to pennysaver, I realise this, what I amtrying to achieve is a reference in cell C2 to cell C2 in another sheet, however I would like the name for the cell in the reference to come from cell B2.

I tried richards' method and all I get is a #ref! comment.

abshaw, sorry the sheet names arnt generic, they are just to make it confusing named, A1, A2, B1, B2, C1, C2,.... etc
So I have a sheet named summary, where I want to have a table refering to cells in each of the sheets. as there is a bunch of sheets I dont want to have to go through and rename, or link each cell so I was hoping for a formula that I could use to refer the links to other sheets to an adjacent column which would have a list of the sheet names.

So if cell B1 in the summary sheet has the name A1 in it, and cell C1 has a reference to Cell C43 in sheet A1. Then can I make a formula to put in Cell C1 in the summary sheet that will refer to cell B1 to get the sheet name, in this case A1! confused?

Thanks

#### AlexN

##### New Member
RichardS, sorry but you were correct, abit more playing around showed that the dollar sign you had there was important, I put it in the formula though, so I ended up with =INDIRECT(A2&"!C2")

Thanks for the solution!!

Replies
1
Views
112
Replies
1
Views
83
Replies
10
Views
81
Replies
17
Views
170
Replies
6
Views
100

1,172,052
Messages
5,878,913
Members
433,383
Latest member
swisshome

### 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.

### Which adblocker are you using?

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

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