 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
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!
Got any Excel Questions? Free Excel Help
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 VBA - Worksheet Names From Cells In Excel Formulas
New & Less Than You Think:List Managers | Working With Excel Sheets In VBA |Excel Charting Lessons | Conditional Row Delete
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates