Can I reference a sheet name in a formula

[JimOTBC]

Hi,

I'm not overly sure how to explain this. I am creating a spreadsheet which has multiple sheets which has a summary sheet at the front which gathers data from all the different tabs. What is becoming a pain is having to rename the formulas to point to a particular tab, so, my question is, can I have a list of the sheetnames which I can then point the formula to, I will try and show this as below:

A	B	C	D	E
1	SheetList	Area	Day	Cost
2	Item1	=cell A2 on sheet Item1	=cell f45 on sheet Item1<strike></strike>	=cell h45 on sheet Item1<strike></strike>
3	Item2	=cell A2 on sheet Item2	=cell f45 on sheet Item2	=cell h45 on sheet Item2
4	Item3	=cell A2 on sheet Item3	=cell f45 on sheet Item3	=cell h45 on sheet Item3
<strike></strike>

<tbody>
</tbody>

So in C2 the formula would be =Item1!A2, what I am trying to avoid is to have to rewrite loads of times. Is there a way for example....and excuse me for being dumb of writing something like =(what ever is written in B2)!A2

Does that make any sense?

 

[Joe4]

Welcome to the Board!

You can use the INDIRECT function to build formulas that dynamically reference a sheet name.
See: http://spreadsheetpro.net/how-to-ma...a-worksheet-in-excel-and-google-spreadsheets/

 

[JoeMo]

If B2 contains this string: Item1!
then in C2 your formula would be: =INDIRECT(B2&"A2")