MS-Excel / Functions and Formula

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"))
[Previous] [Contents] [Next]

In this tutorial:

  1. Return a Worksheet Name to a Cell
  2. Create a List of Worksheet Names
  3. Extract Worksheet Names Only
  4. Use the List in Formulas
  5. Make the Range Address Variable