Excel Formulae Using Worksheet Names in Cells

RETURN WORKSHEET NAMES TO CELLS

There is sometimes a need to have a Worksheet name in a cell as a variable and to use that Worksheet name in a formula. This then enables one to switch Worksheet names and have one single formula able to return results from all Worksheets.

CREATE A LIST OF WORKSHEET NAMES

This is done with relative ease by the use of the CELL function/formula. In any existing *saved Workbook* with existing data already in it (we are using the These 2 Workbook Downloads . On a new Worksheet (name this "Worksheets") add the heading "Names" to A1. Now, in A2 Enter the formula below. It will return the Workbook name, file path and Worksheet name. We will pull out what we need (Worksheet name) soon.

=CELL("filename",Sheet1!$A$1)

Where Sheet1 is the name of the 1st Worksheet in your Workbook, excluding the one we just added and named "Worksheets".

Now copy this formula down as many rows as you have Worksheets. Change each occurrence of "Sheet1" to the names of your other Worksheets. Leave "!$A$1"as is.

The reason we referenced A1 (can be any cell) on each specific sheet is so that when/if the Worksheet name changes, so does our CELL formulas/functions. Also, if no Worksheet is specified, that is

=CELL("filename",A1)

the Worksheet name will ALWAYS be the current active worksheet. This will be an issue when we reference the list from another Worksheet.

EXTRACT OUT ONLY THE WORKSHEET NAMES

In B2 Enter the formula below

="'"&MID(A2,FIND("]",A2)+1,256)&"'!"

Which, in my example would return: 'Sheet1'!

Note the use of the 2 single apostrophes. This allows for Worksheet names that have spaces in their name. It is not needed for Worksheet names with no spaces, but it doesn't do any harm to cover your bases. That is, should you change the Worksheet name to include a space.

Copy the formula above down so that all the data in Column A is referenced. In B1 Enter the heading "Worksheet Names". Highlight/select B1 down until the last formula row in Column "B". Now go to Insert>Name>Create. Ensure only "Top Row" is checked and click OK. Excel will now have created the named range "Worksheet_Names" and omitted B1. See about Named Ranges here

USING THE LIST IN FORMULAS

Add another new worksheet, and name it "Formulas" and use this for the Formulas
that we will add. Let's say now 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>Validation from the menu bar choose "List" then in the "Source:" box add =Worksheet_Names and click OK. With this cell still selected click in the Name Box (left of formula bar) and type: SheetNames and then push Enter.

Ok, in any cell add a VLOOKUP and INDIRECT formula. Change "Mouse" and "A1:D10" to suit any existing data table in your workbook. Also ensure you have chosen a Worksheet name from the list in the named range "SheetNames"

=VLOOKUP("Sales",INDIRECT(SheetNames&"A1:G7"),2,FALSE)

and/or use the SUM formula example below

=SUM(INDIRECT(SheetNames&"B1:B7"))

MAKE THE RANGE ADDRESS VARIABLE

You may wish to make the range references in the formulas variable depending on which worksheet is chosen from our list in the named range
"SheetNames"

Go back to the Worksheet we added and called "Worksheets" in C1 Enter the name "Range". In C2 downwards add range references that you want to correspond to each Worksheet name. For example, A1:G7 may correspond to Sheet1 in B2 and so would go in C2, G9:M15 may
correspond to Sheet2 in B3 ands so would go in C3 etc. You can use range names in place of cell addresses.

Select C1 and highlight down until the last formula row in Column "C". Nowgo to Insert>Name>Create. Ensure only "Top Row" is checked and click OK. Excel will now have created the named range "Range" and omitted C1. Now select B1:C<last row> (don't Start from A1) and click in the Name Box (left of formula bar) and type: MyTable and then push Enter.

Come back to the Worksheet (Formulas) we added the range name "SheetNames" to. In the cell next to this (B2) add this formula

=VLOOKUP(SheetNames,MyTable,2,FALSE)

Click back in this formula cell and name it "RangeLook". Now use the two formulas below in place of the VLOOKUP and SUM formulas shown above

=VLOOKUP("Sales",INDIRECT(SheetNames&RangeLook),2,FALSE)

=SUM(INDIRECT(SheetNames&RangeLook))

Should you wish, you can use Data Validation to List the range name: =Range and change ranges at will.

To have Worksheet names automatically added/deleted as Worksheets are added/deleted see EXCEL VBA TIPS AND TRICKS

Got any Excel Questions? Free Excel Help

IMPORTANT. To understand this page read this page first. Variable Worksheet Names in Excel Formulas

DYNAMIC WORKSHEET NAME LIST Download Workbook Example

The first thing we should do is create a dynamic named range for our list of Worksheet names. Go to Insert>Name>Define and use the name "Worksheet_Names" and have it refer to:

=OFFSET($B$2,0,0,COUNTA($B$2:$B$20),1)

Next we need a macro that we can Call which will add the 2 needed formulae.

One that adds the CELL formula automatically when we add a new Worksheet.

One that adds the MID and FIND formula that parses out the Worksheet name from the CELL formula.

The code for this goes in a standard public module and is as shown below

Sub AddCellFormulas(strShName As String)    With Sheet1.Cells(Rows.Count, 1).End(xlUp)        .Offset(1, 0).FormulaR1C1 = _            "=CELL(""filename""," & strShName & "!R1C1)"        .Offset(1, 1).FormulaR1C1 = _            "=""'""&MID(RC[-1],FIND(""]"",RC[-1])+1,256)&""'!"""    End WithEnd Sub

"Sheet1" is the Worksheets CodeName

Note the Procedure takes an argument as a String and this String is then used in the CELL formula that is added.

Next we need to access the Private Module of the Workbook Object (ThisWorkbook). If in the VBE. Simply double click ThisWorkbook. If in Excel proper, right click on Excel icon, top left next to File and choose View Code. It is in here we use the code below.

Private Sub Workbook_NewSheet(ByVal Sh As Object)    If Sh.Type = xlWorksheet Then        Call AddCellFormulas(Sh.Name)    End IfEnd Sub

In the Event code (which fires anytime a Sheet is added) it first checks to ensure the Sheet Type is a standard Excel Worksheet. If it is, the code uses Call to run the Procedure "AddCellFormulas" and parses the all important worksheet name to it.

Next we need some more Event code that will clear out any redundant formula (added by "AddCellFormulas") when/if a Worksheet is deleted. If a worksheet is deleted the CELL formula that used its name within will result in #REF! as the Worksheet no longer exists. This will result in the MID and FIND formula also returning #REF! as it references a #REF! cell. With this in mind we can use the Calculate Event of the Worksheet Object housing these formulae to clear any error cells. Right click on the Worksheet name (one that AddCellFormulas adds the formulas to) and choose View Code. In here paste the code below.

Private Sub Worksheet_Calculate()    On Error Resume Next    Application.EnableEvents = False        With Me.UsedRange            .SpecialCells(xlCellTypeFormulas, xlErrors).Clear            .Sort Key1:=Range("B2"), Order1:=xlAscending, _                Header:=xlYes, OrderCustom:=1, _                MatchCase:=False, Orientation:=xlTopToBottom        End With    Application.EnableEvents = True    On Error GoTo 0End Sub

Now, each time we add a new Worksheet, we will have the new name in the Validation list. Anytime we delete a Worksheet its name will be cleared and the Worksheet sorted to prevent blanks.

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

OzGrid Excel and VBA Newsletter October 2006

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS