Returning contents of a cell in another sheet in same workbook

[Guest]

I am using multiple sheets. I have a defined name set up in each sheet.
For example I am in Sheet 1. In sheet 1, col a I have a list of the other
Sheets, like Sheet 2, Sheet 3 etc etc.

In Col 2 of Sheet 1 I want to return the value in B2 of Sheet 2 using the
sheet 2 name that I have listed in Col a of sheet 1

Does this make sense

 

[Pete_UK]

Try this in B1 (assuming your names start in A1):

=INDIRECT("'"&A1&"'!B$2")

and copy this down. Note the use of apostrophes in case you have
spaces in your names - the items after the first bracket are
<quotes><apostrophe><quotes> and after the second & it is
<quotes><apostrophe>. I assume you always want data from cell B2 of
the other sheets - hence the use of $ before the 2 so it does not
change to B3, B4 etc when you copy it down.

Hope this helps.

Pete