How to reference a different worksheet's title in a formula

[markmarhon]

Is it possible to reference the name of another worksheet in a formula?
For example, if I have a set of data with fields for January, February, etc., and have tabs that are named January, February, etc., and I want to lookup data in those tables based on the month specified in the field, is there a way to reference the field name to populate the different worksheet name in the formula?

 

[Joe4]

Welcome to the Board!

Yes, you can use the INDIRECT function for that.
See: http://www.cpearson.com/excel/indirect.htm
and https://www.ablebits.com/office-addins-blog/2015/02/10/excel-indirect-function/

 

[jim may]

At its simplest...


Excel 2010

	A	B
Z00001	ABC

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]My Sheet
Named[/TD]
[TD="align: center"]Cell E6 Value[/TD]

[TD="align: center"]2[/TD]

</tbody>

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDIRECT($A2&"!"&"E6")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]