Make the Range Address Variable
You might want to make the range references in the formulas variable, depending on which worksheet is chosen from your list in the named range SheetNames.
Go back to the Worksheets worksheet you added, and enter the name Range in C1. In C2 downward, add range references that you want to correspond to each worksheet name. For example, A1:G7 might correspond to Sheet1 in B2 and so would go in C2, G9:M15 might correspond to Sheet2 in B3 and so would go in C3, and so on.
Select C1 and highlight down until the last formula row in Column C. Now, select Formulas → Defined Names → Create from Selection (pre-2007, Insert → Name → Create). Ensure that only Top Rowis checked and click OK. Excel will create the named range Range, omitting C1. Select B1:C<last row> (don't start from A1), click in the Name Box (left of formula bar), type MyTable, and press Enter.
Come back to the worksheet (Formulas) you added the range name SheetNames to, and delete the formulas you created in cells A4 and A7.
In the cell next to this (B1), add the following formula:
=VLOOKUP(SheetNames,MyTable,2,FALSE)
Click back in this formula cell, and name it RangeLook. Use the two formulas that follow in place of the previous VLOOKUP and SUM formulas.
In cell A4 type the following formula:
=VLOOKUP("Sales",INDIRECT(SheetNames&RangeLook),2,FALSE)
In cell A7, type the following formula:
=SUM(INDIRECT(SheetNames&RangeLook)),
Now, depending on which worksheet is selected from your list in the named range SheetNames, the corresponding range on the worksheet selected will be displayed in cell B1, and therefore the displayed range will be used in the calculations in A4 and A7.
Should you wish, you can use Data Validation to list the range name =Range and change ranges at will.
