# 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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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

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

Do you mean like this?
Book2
ABCD
1
2Sheet2!100100
3Sheet3!300300
Sheet1

Richard

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

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
12
Views
190
Replies
3
Views
56
Replies
9
Views
320
Replies
1
Views
193
Replies
2
Views
80

1,217,449
Messages
6,136,696
Members
450,025
Latest member
Beginner52

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