Sheet Refs

[AlexN]

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

 

[abshaw]

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]

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]

Do you mean like this?

Book2
	A	B	C	D
1
2	Sheet2!	100	100
3	Sheet3!	300	300
Sheet1

Richard

 

[AlexN]

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]

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