108 | Chapter 3, Naming Hacks
#46
Create Custom Functions Using Names
HACK
The dollar sign (
$
) forces any range to be absolute.
When you use the name
MyNumbers
in a formula, it always will refer to the 10
cells immediately above the formula. If you use
=SUM(MyNumbers)
in cell A11
of another worksheet, it still will refer to cells A1:A10 on the sheet that was
active when you originally created the range name.
Simplify the summing. If you want to simplify the summing of the 10 cells,
try these steps:
1. Select cell A11 on any worksheet.
2. Right–click, select Name a Range (pre-2007, Insert
Name
Define),
and type
MySum
in the Name: box.
3. In the Refers To: box, type
=SUM(A$1:A$10)
and click OK.
4. Now enter the number
1
in cell A1.
5. Select cell A1, move your cursor to the fill handle, and press the left
mouse button. Hold down the Ctrl key and drag down to cell A10.
6. Enter
1
in cell B1, and drag down to cell B10 without holding down the
Ctrl key.
7. In cell A11, enter
=MySum
.
8. In cell B11, enter
=MySum
.
You will get the same results you got before, but without requiring the
SUM
function. Mixing up the abgsolute and relative references and nesting a few
functions together can be very handy and can save a lot of work.
H A C K
#46
Create Custom Functions Using Names
Hack #46
Although referencing data by name is convenient, it’s sometimes more
helpful to store a constant value or even a formula, especially if you’ve been
creating custom functions in VBA.
Assume you have a tax rate of 10 percent, which you need to use through-
out your workbook for various calculations. Instead of entering the value
10%
(
0.1
)into each formula that requires this tax rate, you can enter the
word
TaxRate
and Excel will automatically know that
TaxRate
has a value of
0.1
. Here are the steps:
1. Select the Formulas tab and then select Defined Names
Define Name
(pre-2007, Insert
Name
Define).
2. Type
TaxRate
in the Names: box.
Pdf rotate one page - rotate PDF page permanently in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Empower Users to Change the Rotation Angle of PDF File Page Using C#
rotate pdf page permanently; rotate all pages in pdf and save
Pdf rotate one page - VB.NET PDF Page Rotate Library: rotate PDF page permanently in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
PDF Document Page Rotation in Visual Basic .NET Class Application
pdf save rotated pages; how to rotate just one page in pdf
Create Custom Functions Using Names #46
Chapter 3, Naming Hacks | 109
HACK
3. In the Refers To: box, enter
=0.1
and then click Add.
From this point on, you can enter any formula into any cell, and instead of
adding 10 percent as part of the calculation, you can use the word
TaxRate
.
Probably one of the biggest advantages to using this method is that if and
when your tax rate increases or decreases, and your formulas need to reflect
this newpercentage, you can select the Formulas tab, choose Defined
Names
Define Name (pre-2007, Insert
Name
Define), then select the
name
TaxRate
and just modify it to suit.
To take things a step further with this concept, you can use formulas as your
Refers To
:range rather than a cell address or constant value. Suppose you
want to create a name that, when entered into a cell, automatically returns
the
SUM
of the 10 cells immediately above it. Follow these steps:
1. Select cell A11 on any worksheet, right–click, and go to Name a Range
(pre-2007, select Insert
Name
Define).
2. In the Name: box, type the name
Total
,and in the Refers To: box type
=SUM(A1:A10)
. Click OK.
3. Enter any 10 numbers in any column starting from row 1.
4. Nowcome down to row11 of the same column and type
=Total
.The
name
Total
automatically will return the
SUM
of the 10 cells you just
entered in A1:A10.
If you want to create a similarly named formula that is not restricted to only
10 cells, but rather, includes all the cells directly above whatever row hap-
pens to contain
=Total
, follow these steps:
1. Select cell B11, go to the Formulas tab, and select Defined Names
Name Manager (pre-2007, select Insert
Name
Define). Click the
name
Total
.
2. Examine the Refers To: box, which will say
=SUM(B1:B10)
.This enables
you to create named formulas. In other words, because you did not
make the column references absolute for the original name
Total
, it
always will reference the column you use it in.
3. Now, click the Refers To: box and change the formula to
=SUM(B$1:B10)
.
Click OK.
From this point on, you can select any rowin any column other than row1
and enter
=Total
,and you automatically will get the
SUM
of all the cells above
where you enter this, regardless of how many rows there are. This is because
you anchored the rownumber 1 by making it an absolute reference, yet left
the reference to cell B10 as a relative reference, meaning it always will end
up being the cell immediately above where you entered the named formula
=Total
.
VB.NET PDF Page Delete Library: remove PDF pages in vb.net, ASP.
If you are looking for a solution to conveniently delete one page from your PDF document, you can use this VB.NET PDF Library, which supports a variety of PDF
rotate pdf page; rotate pdf pages on ipad
C# PDF File Merge Library: Merge, append PDF files in C#.net, ASP.
C# developers can easily merge and append one PDF document to document imaging toolkit, also offers other advanced PDF document page processing and
how to rotate all pages in pdf at once; how to rotate one page in pdf document
110 | Chapter 3, Naming Hacks
#46
Create Custom Functions Using Names
HACK
Using Names with Intersect
By combining this hack with the intersect operator (one of Excel’s standard,
although little known, features), it’s possible to create sophisticated lookup
functions. If you are not aware of how the intersect method works, here is a
small example to get you acquainted:
1. In cell A1, enter the heading
Name
,in cell B1, enter the heading
Pay
,and
in cell C1, enter the heading
Title
.
2. Enter
Bill
in cell A2 and
Fred
in cell A3.
3. Enter
10
in cell B2 and
20
in cell B3.
4. Enter
Mr
in cell C2 and
Dr
in cell C3.
5. Now, select the range A1:C3, go to the Formulas tab, and choose
Defined Names
Create from Selection (pre-2007, select Insert
Name
Create). Ensure that both the top rowand left column check-
boxes are checked, then click OK.
At this point, if you select any cell outside your table and enter
=Fred Title
,
you should get the correct title for the name Fred.
The space between the words Fred and Title is important, as
this is what Excel understands as the intersect operator.
Building on this concept, you can combine this capability with Excel’s
named formula capabilities to again make your spreadsheets not only easier
to use, but also much easier to read and understand, as the following exam-
ple will illustrate.
First, assume that you have a table set up on a spreadsheet in a fashion simi-
lar to that shown inFigure3-2, and that you are using this table to create
your names in Excel.
Once you create the names for the table, you will see that Excel automati-
cally places an underscore in the spaces between two or more words. This is
because the names of named ranges cannot contain a space. Next, follow
these steps:
1. Right-click and select Name a Range (pre-2007, Insert
Name
Define).
2. Enter
Select
in the Names: box and
FredsPayRate
in the Names in
Workbook: box.
3. In the Refers To: box, type
=Fred_Jones Pay_Rate
and then click OK, as
shown inFigure3-3.
VB.NET PDF File Merge Library: Merge, append PDF files in vb.net
all. This guiding page will help you merge two or more PDF documents into a single one in a Visual Basic .NET imaging application.
reverse page order pdf online; rotate all pages in pdf file
C# PDF Page Extract Library: copy, paste, cut PDF pages in C#.net
Extract PDF Pages, Copy Pages from One PDF File and Paste into Others in C#.NET Program. Free PDF document processing SDK supports PDF page extraction, copying
reverse page order pdf; how to rotate all pages in pdf
Create Custom Functions Using Names #46
Chapter 3, Naming Hacks | 111
HACK
Figure 3-2. Shows Table and Create Names dialog
Figure 3-3. Created Name look-up
VB.NET PDF Annotate Library: Draw, edit PDF annotation, markups in
to display it. Thus, PDFPage, derived from REPage, is a programming abstraction for representing one PDF page. Annotating Process.
rotate pdf pages by degrees; how to change page orientation in pdf document
C# PDF Page Delete Library: remove PDF pages in C#.net, ASP.NET
Using RasterEdge Visual C# .NET PDF page deletion component, developers can easily select one or more PDF pages and delete it/them in both .NET web and Windows
pdf reverse page order preview; rotate pdf page by page
112 | Chapter 3, Naming Hacks
#47
Create Ranges That Expand and Contract
HACK
Nowwhen you enter
=FredsPayRate
in any cell outside your table, the pay
rate for Fred will be returned automatically.
You might want to experiment with intersections to see how they work best
in your projects.
H A C K
#47
Create Ranges That Expand and Contract
Hack #47
If you need to constantly update and add to your data, or if you work with
charts and PivotTables, you’ll want to create dynamic named ranges, which
expand and contract relative to your data.
To understand howdynamic named ranges function, first you should famil-
iarize yourself with Excel’s
OFFSET
function (if you haven’t already). The
OFFSET
function is one of Excel’s Lookup and Reference functions.
We’ll start off with the simplest of dynamic named ranges, one that will
expand down a single column, but only as far as there are entries in that col-
umn. For example, if column A contains 10 continuous rows of data, your
dynamic named range will incorporate the range A1:A10. Follow these steps
to create a basic dynamic named range.
Under the Formulas tab, select Defined Names
Define Name (pre-2007,
Insert
Name
Define) and type
MyRange
in the Names: box. In the Refers
To: box, type the following:
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100),1)
Now click Add, then OK.
When defining the range for
COUNTA
,resist the temptation to
include an entire column of data so that you do not force the
COUNTA
function to count potentially thousands of unneces-
sary cells.
Now, provided that you have some data in column A, this named range will
incorporate all the data in continuous rows, starting from cell A1. If you
want to check a dynamic named range, you can do so in a few ways.
Unfortunately, dynamic named ranges are not available via the standard
Name box, immediately to the left of the Formula bar. Despite this, you can
click the Name box, type the name
MyRange
,and press Enter. Excel will auto-
matically select the range. Of course, you also can use the Go To... dialog by
going to the Home tab and selecting Find & Select
Go To… under Editing
options (Ctrl/
c
-G; pre-2007, Edit
Go To…). Enter
MyRange
in the Refer-
ence: box and click OK.
C# PDF: C# Code to Process PDF Document Page Using C#.NET PDF
for developers on how to rotate PDF page in different two different PDF documents into one large PDF C# PDF Page Processing: Split PDF Document - C#.NET PDF
pdf rotate single page and save; rotate one page in pdf
C# PDF copy, paste image Library: copy, paste, cut PDF images in
This C#.NET example describes how to copy an image from one page of PDF document and paste it into another page. // Define input and output documents.
rotate all pages in pdf; rotate one page in pdf reader
Create Ranges That Expand and Contract #47
Chapter 3, Naming Hacks | 113
HACK
The dynamic named range you created in the previous example nests the
COUNTA
function as the
Height
argument in the
OFFSET
function.
Remember that
COUNTA
will count all nonblank cells. Be
aware that this also will include formulas you have in those
cells, which might be returning empty text (
""
).
If you have a list that contained numeric data only, and at the end of this list
you want to store text, but don’t want this text included as part of your
dynamic named range, you could replace the
COUNTA
function with Excel’s
standard
COUNT
function.
COUNT
counts only cells containing numeric data.
In this next example, you will use a dynamic named range to define a table
of data that you want to be dynamic. To do this, type the following func-
tion into the Refers To: box:
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100),COUNTA($1:$1))
Here, the dynamic named range will expand down as many entries as there
are in column A, and across as many rows as there are headings in row 1. If
you are sure the number of columns for your table of data will remain sta-
ble, you can replace the second
COUNTA
function with a fixed number such as
10.
The only problem with using a dynamic named range for a table of data is
that it assumes column A will set the maximum length for the table. In most
cases, this probably will be true; however, sometimes the longest column
might be another column on the spreadsheet.
To overcome this potential problem, you can use Excel’s
MAX
function,
which returns the highest number in a range of cells. As an example, set up a
table in a manner similar to the one shown inFigure3-4.
Use row1 to store a number of
COUNTA
functions that are referencing down
the column and, thus, returning the number of entries in each column. Use
the
MAX
function for the
Height
argument in the
OFFSET
function. This
ensures that the dynamic named range for the table always will expand
down as far as the longest column in the table. Of course you can hide row
1, as there is no need for a user to see it.
In all these examples, you assumed your data will always be in continuous
rows without blank cells in between. Although this is the correct way to set
up a list or a table of data, sometimes you have no control over this.
In the next example, the list of numbers in column A also contains blank
cells. This means that if you try to use the
COUNT
or
COUNTA
function, the
114 | Chapter 3, Naming Hacks
#47
Create Ranges That Expand and Contract
HACK
dynamic named range will fall short of the real last cell containing any data.
For example, considerFigure3-5.
In this case, although the last number in the range is actually in row10, the
dynamic range is expanding down to row 6. This is because you used the
COUNT
function to count from A1 to A100. Only six numeric entries are in
the list, so the range expands down only six rows.
To overcome this problem, use Excel’s
MATCH
function. The
MATCH
function is
used to return the relative position of an item in an array that matches a
specified value in a specified order. For example, if you use this
MATCH
function:
=MATCH(6,$A$1:$A$100,0)
on the same set of numbers shown inFigure3-5, the
MATCH
function will
return the number
10
, representing row10 in column A. It returns
10
because you told the function to find the number 6 in the range A1:A100.
Figure 3-4. Dynamic table of data and the Define Name dialog
Create Ranges That Expand and Contract #47
Chapter 3, Naming Hacks | 115
HACK
Obviously, when using the
MATCH
function as part of a dynamic named
range, the last number in the range probably is not known in advance.
Therefore, you need to tell the
MATCH
function to try and locate a ridicu-
lously high number in the range that would never exist and to swap the last
argument for the
MATCH
function from 0 to 1.
In the previous example, you told
MATCH
to find the exact number 6, nothing
less and nothing more. Replacing
0
with
1
tells
MATCH
to locate the largest
value that is less than or equal to that value.
To do this, use this formula:
=MATCH(1E+306,$A$1:$A$100,1)
To create a dynamic named range that will expand down to the last row that
contains a number (regardless of the blank cells in between), type that for-
mula into the Refers To: box of the Name Manager dialog, as illustrated in
Figure 3-6.
=OFFSET(Sheet2!$A$1,0,0,MATCH(1E+306,Sheet2!$A$1:$A$100,1),1)
The next logical type of dynamic named range that would flow on from this
is one that will expand down to the last text entry, regardless of any blank
cells in the list or table.
Figure 3-5. Range of numbers and Define Name dialog
116 | Chapter 3, Naming Hacks
#47
Create Ranges That Expand and Contract
HACK
To do this, replace the
MATCH
function with the following:
MATCH("*",$A$1:$A$100,-1)
This always will return the row number for the last text entry in range
$A$1:$A$100.
Nowthat you knowhowto do this for numeric entries and text entries, it is
only logical that you need to somehowdefine a dynamic named range that
will look past blank cells in a list that contains both text and numeric data.
To do this, first insert two blank rows above your list by selecting rows 1
and 2. Then, select Home
Cells
Insert (pre-2007, Insert
Row). In the
first row (row 1), add this function:
=MAX(MATCH"*",$A$2:$A$100,-1),MATCH(1E+306,$A$2:$A$100,1))
In the cell immediately belowthis, type the number
1
.The cell belowthis
must contain a text heading for your list. You added the number 1 so that
the second
MATCH
function does not return
#N/A
when or if there are no num-
bers in A3:A100. The second
MATCH
function will always find text because
you have a heading.
Name cell A1
MaxRow
,right-click, and select Name a Range (pre-2007, Insert
Name
Define). Give the dynamic range a name, such as
MyList
,and
type the following function in the Refers To: box, as shown inFigure3-7:
=OFFSET(Sheet1!$A$3,0,0,MaxRow,1)
Figure 3-6. A dynamic range extending to the last numeric entry
Create Ranges That Expand and Contract #47
Chapter 3, Naming Hacks | 117
HACK
The following list outlines other types of dynamic named ranges you might
find useful. For all of these examples, you will need to fill column A with a
mix of text and numeric entries. To do this, right-click and select Name a
Range (pre-2007, Insert
Name
Define). In the Names: box, type any
one-word name (for instance,
MyRange
). The only part that will change is the
formula you place in the Refers To: box:
Expand down as many rows as there are numeric entries
In the Refers To: box, type the following:
=OFFSET($A$1,0,0,COUNT($A:$A),1)
Expand down as many rows as there are numeric and text entries
In the Refers To: box, type the following:
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
Expand down to the last numeric entry
In the Refers To: box, type the following:
=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A))
If you expect a number larger than 1E+306 (a 1 with 306 zeros), change
this to a larger number.
Expand down to the last text entry
In the Refers To: box, type the following:
=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))
Figure 3-7. Dynamic list for numeric and text entries containing blanks
Documents you may be interested
Documents you may be interested