LOOK! Free Excel Training << Back to Excel Formulas Index << Back to Excel Named Ranges
Now we have seen how easy Named
Ranges and Named Constants
are, we can look at a little known ability of Names where they can used to
create custom formulas. For example, let's suppose we frequently use
a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is
copied across many columns on row 101. It would be better in this case to
create a custom formula that does this in each cell on row 101. Here is how;
1) Select cell A101 (this is vital).
2) Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts
3) Now click in the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100) then click Add.
Now you can replace the formula in cell A101 with: =SalesLessCosts. You can also copy this across row 101 and it will change its relative references just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected A101 before going to Insert>Name>Define and used relative references in =SUM(A1:A100)-SUM(B1:B100) when we added it to the "Refers to" box.
You could force full Absolute simply by using: =SUM($A$1:$A$100)-SUM($B$1:$B$100) or relative row absolute column like: =SUM($A1:$A100)-SUM($B1:$B100) or, of course any mix of absolute/relative row/column you desire. This can be a bit confusing at first, but some trial and error along with being aware of the selected cell when going to Insert>Name>Define will help a lot!
Use the Named Formula on Another Worksheet
Let's say you wanted to have these results only on another sheet to where the $A$1:$A$100 and $B$1:$B$100 ranges are. For a standard formula you would use a formula like: =SUM(Sheet1!$A$1:$A$100)-SUM(Sheet1!$B$1:$B$100). However, we cannot use: =Sheet1!SalesLessCosts as Excel will assume you mean the Workbook name and change it accordingly. Without it, Excel will sum the *relative* cells on the same Worksheet as you Enter: =SalesLessCosts
What we need to do is precede the formula name in the "Names in workbook" box of Insert Name dialog with: 'Sheet1'! The single apostrophes are not required when the sheet name has no space, but are when a space character is used as part of the Worksheet name, i.e. "Sheet 1". With this in mind, it pays to use them anyway as it won't matter.
1) Select cell A101 (again, this is vital).
2) Go to Insert>Name>Define and in the "Names in workbook" box replace: SalesLessCosts with 'Sheet1'!SalesLessCosts and click Add.
Now, in the required cell simply Enter: ='Sheet1'!SalesLessCosts
<< Back to Excel Formulas Index << Back to Excel Named Ranges
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. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical 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