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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SheetList[/TD]
[TD]Area[/TD]
[TD]Day[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item1[/TD]
[TD]=cell A2 on sheet Item1[/TD]
[TD]=cell f45 on sheet Item1<strike></strike>[/TD]
[TD]=cell h45 on sheet Item1<strike></strike>[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item2[/TD]
[TD]=cell A2 on sheet Item2[/TD]
[TD]=cell f45 on sheet Item2[/TD]
[TD]=cell h45 on sheet Item2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item3[/TD]
[TD]=cell A2 on sheet Item3[/TD]
[TD]=cell f45 on sheet Item3[/TD]
[TD]=cell h45 on sheet Item3[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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