Sheet Refs

AlexN

New Member
Joined
Apr 8, 2002
Messages
7
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53
quick question before i try to answer your question.

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
Joined
May 15, 2003
Messages
29,536
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
 

AlexN

New Member
Joined
Apr 8, 2002
Messages
7
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
Joined
Apr 8, 2002
Messages
7
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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
Top