Microsoft Office Tutorials and References
In Depth Information
Setting up the supporting worksheet
The Top Regions table uses formulas to return region names and
scores from some external database. This could be an OLAP or
relational database, or even a spreadsheet database. If these
formulas run out of data, they must return a value that other
formulas can recognize. Here, the formulas return #N/A. But they
could return null strings, “xxx”, or whatever.
The sequence numbers beginning in cell A7
count the number of rows of data in the current
table. Cell A7 contains the value 1. Cell A8
contains this formula:
Top Regions
# e i n e
1 New York
193
2 Calif ornia
179
3 Kansas
165
4 Alabama
161
A
B
C
=A7+IF(ISERROR(C8),0,1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DataRows
5
MaxRows
4
The formula is copied down the remainder of
column A.
UseRows
4
Top Regions
#
Region
Score
1 New York
193
This column is useful, because its largest
value tells us the maximum number of regions
available. Cell C1 returns this value with the
formula: =MAX(A6:A17)
2 California
179
3 Kansas
165
4 Alabama
161
5 Montana
157
5
#N/A
#N/A
5
#N/A
#N/A
5
#N/A
#N/A
Cell C2 contains a manually entered value,
which specifies the maximum number of rows
of data that we have room for in our dashboard report.
5
#N/A
#N/A
5
#N/A
#N/A
Cell C3, named UseRows, contains the formula:
=MIN(MaxRows,DataRows)
The formula says that the final table can contain the maximum
number of rows we’ve specified, or the number of rows of data
available, whichever is less.
Search JabSto ::

Custom Search