Use the List in Formulas
Add another neww orksheet, named Formulas, to use for the formulas we will add. Let's say you are doing a VLOOKUP and/or SUM on a worksheet (any worksheet except the one housing the formulas and worksheet names) and you need variable worksheet names. Select A1 (any cell) and go to Data → Data Options → Data Validation (pre-2007, Data → Validation). Select List from the Allow: box, type =Worksheet_Names in the Source: box, and click OK. With this cell still selected, click in the Name Box (left of formula bar), type SheetNames, and press Enter.
Now, add the following VLOOKUP and INDIRECT function in cell A4:
=VLOOKUP("Sales",INDIRECT(SheetNames&"A1:G7"),2,FALSE)
Make sure you have chosen a worksheet name from the list in the named range SheetNames.
In cell A7, enter the following SUM and INDIRECT function:
=SUM(INDIRECT(SheetNames&"B1:B7"))