
LOOK! Free Excel Training << Back to Excel Formulas Index << Back to Excel Named Ranges
Named Formulas
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 Addins 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 [email protected] 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 & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates