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.